-- ScriptSync: M3U -> XUI One + Séries + Asaas + Vendas
-- Execute em instalações existentes. Em instalação nova, use database/install.sql.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

ALTER TABLE sources ADD COLUMN import_series TINYINT(1) NOT NULL DEFAULT 1 AFTER import_vod;
ALTER TABLE categories MODIFY COLUMN type ENUM('live','vod','series') NOT NULL DEFAULT 'live';
ALTER TABLE source_categories MODIFY COLUMN type ENUM('live','vod','series') NOT NULL DEFAULT 'live';
ALTER TABLE sync_jobs MODIFY COLUMN type ENUM('all','live','vod','series') NOT NULL DEFAULT 'all';

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 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 settings (`key`,`value`,created_at,updated_at) VALUES
('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;