-- PTY ENTERTAINMENT - Esquema de base de datos (MySQL 5.7+/8)
-- Importar desde phpMyAdmin (cPanel) o: mysql -u USER -p DB < database.sql

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- Administradores del panel
CREATE TABLE IF NOT EXISTS admins (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(60)  NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Usuarios de la app (clientes)
CREATE TABLE IF NOT EXISTS users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(60)  NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  expires_at    DATE NULL,                 -- opcional: vencimiento de cuenta
  created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Categorías (sirven para VOD y para En vivo)
CREATE TABLE IF NOT EXISTS categories (
  id        INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  type      ENUM('vod','live') NOT NULL DEFAULT 'vod',
  sort      INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contenido (películas/series VOD y canales en vivo en una sola tabla)
CREATE TABLE IF NOT EXISTS contents (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  kind         ENUM('vod','live') NOT NULL,
  title        VARCHAR(200) NOT NULL,
  description  TEXT NULL,
  poster_url   VARCHAR(500) NULL,          -- póster (VOD) o logo (live)
  stream_url   VARCHAR(500) NOT NULL,      -- URL del video o del stream
  category_id  INT NULL,
  year         SMALLINT NULL,
  rating       DECIMAL(3,1) NULL,
  is_active    TINYINT(1) NOT NULL DEFAULT 1,
  sort         INT NOT NULL DEFAULT 0,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_kind (kind),
  INDEX idx_cat (category_id),
  CONSTRAINT fk_content_cat FOREIGN KEY (category_id)
    REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Favoritos / seguir viendo
CREATE TABLE IF NOT EXISTS favorites (
  user_id    INT NOT NULL,
  content_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, content_id),
  CONSTRAINT fk_fav_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_fav_content FOREIGN KEY (content_id) REFERENCES contents(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tokens de sesión de la app
CREATE TABLE IF NOT EXISTS user_tokens (
  token      CHAR(64) PRIMARY KEY,
  user_id    INT NOT NULL,
  expires_at TIMESTAMP NOT NULL,
  CONSTRAINT fk_token_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;