SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS admins (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(80) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS prizes (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(150) NOT NULL,
 category ENUM('FINAL','CREDITS','APP') NOT NULL,
 weight TINYINT UNSIGNED NOT NULL DEFAULT 0,
 color VARCHAR(7) NOT NULL DEFAULT '#7828e8',
 months SMALLINT UNSIGNED NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1,
 sort_order INT NOT NULL DEFAULT 0,
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_prize_category_active(category,active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS spin_links (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 token_hash CHAR(64) NOT NULL UNIQUE,
 customer_name VARCHAR(120) NOT NULL,
 phone VARCHAR(30) NULL,
 purchase_ref VARCHAR(100) NOT NULL UNIQUE,
 amount DECIMAL(12,2) NOT NULL DEFAULT 0,
 category ENUM('FINAL','CREDITS','APP') NOT NULL,
 mode ENUM('TEST','OFFICIAL') NOT NULL DEFAULT 'TEST',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 expires_at DATETIME NOT NULL,
 used_at DATETIME NULL,
 created_by INT UNSIGNED NOT NULL,
 INDEX idx_link_status(used_at,expires_at),
 CONSTRAINT fk_link_admin FOREIGN KEY(created_by) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS spin_results (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 link_id BIGINT UNSIGNED NOT NULL UNIQUE,
 prize_id INT UNSIGNED NULL,
 prize_name VARCHAR(150) NOT NULL,
 delivery_status ENUM('PENDING','DELIVERED') NOT NULL DEFAULT 'PENDING',
 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 delivered_at DATETIME NULL,
 CONSTRAINT fk_result_link FOREIGN KEY(link_id) REFERENCES spin_links(id),
 CONSTRAINT fk_result_prize FOREIGN KEY(prize_id) REFERENCES prizes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO prizes(name,category,weight,color,months,active,sort_order) VALUES
('1 mes de servicio gratis','FINAL',10,'#762be8',1,1,1),
('3 meses de servicio gratis','FINAL',3,'#9c36ed',3,1,2),
('12 meses de servicio gratis','FINAL',1,'#e9b74e',12,1,3),
('Sigue intentando','FINAL',86,'#15111e',0,1,4),
('10 créditos adicionales','CREDITS',10,'#762be8',0,1,1),
('5 créditos adicionales','CREDITS',20,'#9c36ed',0,1,2),
('Sigue intentando','CREDITS',70,'#15111e',0,1,3),
('Una aplicación gratis','APP',10,'#762be8',0,1,1),
('Descuento en aplicación','APP',10,'#9c36ed',0,1,2),
('Sigue intentando','APP',80,'#15111e',0,1,3);