-- ScriptSync Painel Completo
-- Banco recomendado: MySQL 5.7+ ou MariaDB 10.3+
-- Crie o banco antes ou execute:
-- CREATE DATABASE scriptsync CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE scriptsync;


CREATE TABLE IF NOT EXISTS categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  type ENUM('live','vod','series') NOT NULL DEFAULT 'live',
  is_adult TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uniq_category_name_type (name, type),
  INDEX idx_categories_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS source_categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id INT UNSIGNED NOT NULL,
  external_id VARCHAR(190) NOT NULL,
  name VARCHAR(190) NOT NULL,
  type ENUM('live','vod','series') NOT NULL DEFAULT 'live',
  item_count INT UNSIGNED NOT NULL DEFAULT 0,
  is_adult TINYINT(1) NOT NULL DEFAULT 0,
  selected TINYINT(1) NOT NULL DEFAULT 1,
  map_category_id INT UNSIGNED NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uniq_source_external_type (source_id, external_id, type),
  INDEX idx_source_categories_source (source_id),
  INDEX idx_source_categories_map (map_category_id),
  CONSTRAINT fk_source_categories_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE CASCADE,
  CONSTRAINT fk_source_categories_map FOREIGN KEY (map_category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS channels (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id INT UNSIGNED NOT NULL,
  external_id VARCHAR(190) NOT NULL,
  category_id INT UNSIGNED NULL,
  name VARCHAR(255) NOT NULL,
  stream_url TEXT NOT NULL,
  logo TEXT NULL,
  epg_channel_id VARCHAR(190) NULL,
  tvg_name VARCHAR(255) NULL,
  group_title VARCHAR(190) NULL,
  status ENUM('active','inactive','error') NOT NULL DEFAULT 'active',
  last_seen_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uniq_channel_source_external (source_id, external_id),
  INDEX idx_channels_source (source_id),
  INDEX idx_channels_category (category_id),
  INDEX idx_channels_status (status),
  INDEX idx_channels_name (name),
  CONSTRAINT fk_channels_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE CASCADE,
  CONSTRAINT fk_channels_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vods (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id INT UNSIGNED NOT NULL,
  external_id VARCHAR(190) NOT NULL,
  category_id INT UNSIGNED NULL,
  name VARCHAR(255) NOT NULL,
  stream_url TEXT NOT NULL,
  poster TEXT NULL,
  backdrop TEXT NULL,
  plot TEXT NULL,
  release_date DATE NULL,
  rating VARCHAR(20) NULL,
  duration_secs INT UNSIGNED NULL,
  container_extension VARCHAR(20) NULL,
  tmdb_id VARCHAR(50) NULL,
  status ENUM('active','inactive','error') NOT NULL DEFAULT 'active',
  last_seen_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uniq_vod_source_external (source_id, external_id),
  INDEX idx_vods_source (source_id),
  INDEX idx_vods_category (category_id),
  INDEX idx_vods_status (status),
  INDEX idx_vods_name (name),
  CONSTRAINT fk_vods_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE CASCADE,
  CONSTRAINT fk_vods_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS series (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NULL,
  external_id VARCHAR(190) NOT NULL,
  name VARCHAR(255) NOT NULL,
  poster TEXT NULL,
  plot TEXT NULL,
  release_date DATE NULL,
  rating VARCHAR(20) NULL,
  tmdb_id VARCHAR(50) NULL,
  status ENUM('active','inactive','error') NOT NULL DEFAULT 'active',
  last_seen_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uniq_series_source_external (source_id, external_id),
  UNIQUE KEY uniq_series_source_name (source_id, name),
  INDEX idx_series_source (source_id),
  INDEX idx_series_category (category_id),
  INDEX idx_series_status (status),
  CONSTRAINT fk_series_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE CASCADE,
  CONSTRAINT fk_series_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS episodes (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id INT UNSIGNED NOT NULL,
  series_id BIGINT UNSIGNED NOT NULL,
  external_id VARCHAR(190) NOT NULL,
  season_number INT UNSIGNED NOT NULL DEFAULT 1,
  episode_number INT UNSIGNED NOT NULL DEFAULT 1,
  title VARCHAR(255) NOT NULL,
  stream_url TEXT NOT NULL,
  poster TEXT NULL,
  container_extension VARCHAR(20) NULL,
  status ENUM('active','inactive','error') NOT NULL DEFAULT 'active',
  last_seen_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uniq_episode_source_external (source_id, external_id),
  INDEX idx_episodes_series (series_id),
  INDEX idx_episodes_status (status),
  CONSTRAINT fk_episodes_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE CASCADE,
  CONSTRAINT fk_episodes_series FOREIGN KEY (series_id) REFERENCES series(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bouquets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  description TEXT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bouquet_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bouquet_id INT UNSIGNED NOT NULL,
  item_type ENUM('channel','vod') NOT NULL,
  item_id BIGINT UNSIGNED NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  UNIQUE KEY uniq_bouquet_item (bouquet_id, item_type, item_id),
  INDEX idx_bouquet_items_bouquet (bouquet_id),
  CONSTRAINT fk_bouquet_items_bouquet FOREIGN KEY (bouquet_id) REFERENCES bouquets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sync_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  source_id INT UNSIGNED NULL,
  type ENUM('all','live','vod','series') NOT NULL DEFAULT 'all',
  status ENUM('queued','running','finished','failed') NOT NULL DEFAULT 'queued',
  total_items INT UNSIGNED NOT NULL DEFAULT 0,
  processed_items INT UNSIGNED NOT NULL DEFAULT 0,
  message TEXT NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_sync_jobs_source (source_id),
  INDEX idx_sync_jobs_status (status),
  CONSTRAINT fk_sync_jobs_source FOREIGN KEY (source_id) REFERENCES sources(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sync_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  job_id BIGINT UNSIGNED NOT NULL,
  level ENUM('info','warning','error','debug') NOT NULL DEFAULT 'info',
  message TEXT NOT NULL,
  context JSON NULL,
  created_at DATETIME NULL,
  INDEX idx_sync_logs_job (job_id),
  CONSTRAINT fk_sync_logs_job FOREIGN KEY (job_id) REFERENCES sync_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS xui_push_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  m3u_source_id INT UNSIGNED NULL,
  xui_source_id INT UNSIGNED NULL,
  server_id INT UNSIGNED NOT NULL DEFAULT 1,
  mode ENUM('all','live','vod','series') NOT NULL DEFAULT 'all',
  status ENUM('running','finished','failed') NOT NULL DEFAULT 'running',
  total_items INT UNSIGNED NOT NULL DEFAULT 0,
  processed_items INT UNSIGNED NOT NULL DEFAULT 0,
  live_items INT UNSIGNED NOT NULL DEFAULT 0,
  vod_items INT UNSIGNED NOT NULL DEFAULT 0,
  series_items INT UNSIGNED NOT NULL DEFAULT 0,
  message TEXT NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_xui_push_sources (m3u_source_id, xui_source_id),
  INDEX idx_xui_push_status (status),
  CONSTRAINT fk_xui_push_m3u FOREIGN KEY (m3u_source_id) REFERENCES sources(id) ON DELETE SET NULL,
  CONSTRAINT fk_xui_push_xui FOREIGN KEY (xui_source_id) REFERENCES sources(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS xui_push_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  job_id BIGINT UNSIGNED NOT NULL,
  level ENUM('info','warning','error','debug') NOT NULL DEFAULT 'info',
  message TEXT NOT NULL,
  context JSON NULL,
  created_at DATETIME NULL,
  INDEX idx_xui_push_logs_job (job_id),
  CONSTRAINT fk_xui_push_logs_job FOREIGN KEY (job_id) REFERENCES xui_push_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  description TEXT NULL,
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  duration_days INT UNSIGNED NOT NULL DEFAULT 30,
  max_connections INT UNSIGNED NOT NULL DEFAULT 1,
  xui_source_id INT UNSIGNED NULL,
  xui_bouquet_ids VARCHAR(255) NULL,
  xui_member_id INT UNSIGNED NOT NULL DEFAULT 1,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_plans_enabled (enabled),
  CONSTRAINT fk_plans_xui_source FOREIGN KEY (xui_source_id) REFERENCES sources(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(60) NULL,
  document VARCHAR(40) NULL,
  asaas_customer_id VARCHAR(80) NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uniq_customers_email (email),
  INDEX idx_customers_asaas (asaas_customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NOT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'asaas',
  method VARCHAR(40) NOT NULL DEFAULT 'PIX',
  status ENUM('pending','paid','cancelled','failed','refunded') NOT NULL DEFAULT 'pending',
  amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  asaas_payment_id VARCHAR(80) NULL,
  asaas_invoice_url TEXT NULL,
  asaas_status VARCHAR(80) NULL,
  external_reference VARCHAR(120) NULL,
  raw_payload LONGTEXT NULL,
  paid_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_payments_customer (customer_id),
  INDEX idx_payments_plan (plan_id),
  INDEX idx_payments_asaas (asaas_payment_id),
  CONSTRAINT fk_payments_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_payments_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS asaas_webhook_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event VARCHAR(120) NOT NULL,
  asaas_payment_id VARCHAR(80) NULL,
  payload LONGTEXT NULL,
  created_at DATETIME NULL,
  INDEX idx_asaas_webhook_payment (asaas_payment_id),
  INDEX idx_asaas_webhook_event (event)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

