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

-- Senha inicial: ChangeMe!2026 (troque imediatamente após a instalação)
INSERT INTO admins(username,password_hash) VALUES
('admin','$2y$12$ib7rmZKPJCVLMsZC1Ci8KedQfiT5Szn.xS0M311OYXG/vYvOPBoyy');

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 status ENUM('active','blocked') NOT NULL DEFAULT 'active',
 expires_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE user_tokens (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 token_hash CHAR(64) NOT NULL UNIQUE,
 expires_at DATETIME NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE media (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 type ENUM('movie','series') NOT NULL,
 title VARCHAR(220) NOT NULL,
 description TEXT NULL,
 poster_url VARCHAR(1000) NULL,
 banner_url VARCHAR(1000) NULL,
 year SMALLINT NULL,
 age_rating VARCHAR(20) NULL,
 duration_minutes SMALLINT NULL,
 stream_url VARCHAR(2000) NULL,
 featured TINYINT(1) NOT NULL DEFAULT 0,
 active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(type,active), INDEX(featured,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE episodes (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 media_id BIGINT UNSIGNED NOT NULL,
 season_number SMALLINT NOT NULL,
 episode_number SMALLINT NOT NULL,
 title VARCHAR(220) NOT NULL,
 description TEXT NULL,
 duration_minutes SMALLINT NULL,
 stream_url VARCHAR(2000) NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1,
 FOREIGN KEY(media_id) REFERENCES media(id) ON DELETE CASCADE,
 UNIQUE KEY(media_id,season_number,episode_number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE favorites (
 user_id BIGINT UNSIGNED NOT NULL,
 media_id BIGINT UNSIGNED NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(user_id,media_id),
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY(media_id) REFERENCES media(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;