-- 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;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('admin','operator') NOT NULL DEFAULT 'admin',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  last_login_at DATETIME NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(100) NOT NULL UNIQUE,
  `value` TEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sources (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  type ENUM('xtream','m3u','xui_mysql') NOT NULL DEFAULT 'xtream',
  base_url VARCHAR(500) NULL,
  username VARCHAR(190) NULL,
  password VARCHAR(190) NULL,
  m3u_url TEXT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  import_live TINYINT(1) NOT NULL DEFAULT 1,
  import_vod TINYINT(1) NOT NULL DEFAULT 1,
  import_series TINYINT(1) NOT NULL DEFAULT 1,
  api_delay_ms INT UNSIGNED NOT NULL DEFAULT 150,
  xui_db_host VARCHAR(190) NULL,
  xui_db_port INT UNSIGNED NOT NULL DEFAULT 3306,
  xui_db_name VARCHAR(190) NULL,
  xui_db_user VARCHAR(190) NULL,
  xui_db_pass VARCHAR(255) NULL,
  xui_only_enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_sources_type (type),
  INDEX idx_sources_enabled (enabled),
  INDEX idx_sources_xui_db (xui_db_host, xui_db_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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 customer_subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id BIGINT UNSIGNED NOT NULL,
  plan_id INT UNSIGNED NOT NULL,
  payment_id BIGINT UNSIGNED NULL,
  status ENUM('pending_payment','active','suspended','cancelled','expired','provisioning_error') NOT NULL DEFAULT 'pending_payment',
  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,
  service_username VARCHAR(80) NULL,
  service_password VARCHAR(80) NULL,
  xui_line_id BIGINT UNSIGNED NULL,
  starts_at DATETIME NULL,
  expires_at DATETIME NULL,
  notes TEXT NULL,
  created_at DATETIME NULL,
  updated_at DATETIME NULL,
  INDEX idx_customer_subscriptions_customer (customer_id),
  INDEX idx_customer_subscriptions_status (status),
  CONSTRAINT fk_customer_subscriptions_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  CONSTRAINT fk_customer_subscriptions_plan FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT,
  CONSTRAINT fk_customer_subscriptions_payment FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL
) 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;

INSERT INTO users (name, email, password_hash, role, status, created_at, updated_at)
VALUES ('Administrador', 'admin@local', '$2y$12$Hn8Meolqti1Xq/fUwPTW9u5CR24DCK3bPBjsC4hK65SXIGrhe9HpK', 'admin', 'active', NOW(), NOW())
ON DUPLICATE KEY UPDATE email = email;

INSERT INTO settings (`key`,`value`,created_at,updated_at) VALUES
('auto_sync_enabled','0',NOW(),NOW()),
('auto_sync_hour','03:00',NOW(),NOW()),
('tmdb_api_key','',NOW(),NOW()),
('telegram_bot_token','',NOW(),NOW()),
('telegram_chat_id','',NOW(),NOW()),
('whatsapp_group_id','',NOW(),NOW()),
('asaas_enabled','0',NOW(),NOW()),
('asaas_environment','sandbox',NOW(),NOW()),
('asaas_api_key','',NOW(),NOW()),
('asaas_billing_type','PIX',NOW(),NOW()),
('asaas_webhook_token','',NOW(),NOW())
ON DUPLICATE KEY UPDATE `key` = `key`;

SET FOREIGN_KEY_CHECKS = 1;