

CREATE TABLE IF NOT EXISTS models (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NULL UNIQUE,
  username VARCHAR(120) NULL,
  display_name VARCHAR(180) NULL,
  status ENUM('pending','approved','blocked') NOT NULL DEFAULT 'pending',
  credit_balance INT NOT NULL DEFAULT 0,
  custom_daily_limit INT NULL,
  notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_models_username (username),
  INDEX idx_models_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS blocked_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  telegram_id BIGINT NULL,
  username VARCHAR(120) NULL,
  reason TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_blocked_telegram (telegram_id),
  INDEX idx_blocked_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS credit_packages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  credits INT NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO credit_packages (name, price, credits, active, sort_order) VALUES
('1 divulgação', 9.90, 1, 1, 1),
('3 divulgações', 19.90, 3, 1, 2),
('10 divulgações', 49.90, 10, 1, 3);

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  model_id INT NOT NULL,
  package_id INT NULL,
  amount DECIMAL(10,2) NOT NULL,
  credits INT NOT NULL DEFAULT 0,
  status ENUM('pending','approved','cancelled','expired','refunded') NOT NULL DEFAULT 'pending',
  provider VARCHAR(80) NOT NULL DEFAULT 'manual',
  provider_payment_id VARCHAR(180) NULL,
  external_reference VARCHAR(180) NOT NULL UNIQUE,
  payment_url TEXT NULL,
  qr_code TEXT NULL,
  raw_response MEDIUMTEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  paid_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE,
  FOREIGN KEY (package_id) REFERENCES credit_packages(id) ON DELETE SET NULL,
  INDEX idx_payments_status (status),
  INDEX idx_payments_ref (external_reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS credit_transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  model_id INT NOT NULL,
  payment_id INT NULL,
  type ENUM('add','spend','remove','refund') NOT NULL,
  amount INT NOT NULL,
  reason VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE,
  FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL,
  INDEX idx_credit_model (model_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ads (
  id INT AUTO_INCREMENT PRIMARY KEY,
  model_id INT NOT NULL,
  telegram_user_id BIGINT NOT NULL,
  username VARCHAR(120) NULL,
  final_text TEXT NOT NULL,
  media_file_id TEXT NOT NULL,
  media_type ENUM('photo','video') NOT NULL,
  status ENUM('draft','pending_payment','pending_approval','queued','sending','sent','rejected','failed','cancelled') NOT NULL DEFAULT 'draft',
  credit_spent TINYINT(1) NOT NULL DEFAULT 0,
  rejection_reason TEXT NULL,
  scheduled_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  approved_at TIMESTAMP NULL DEFAULT NULL,
  sent_at TIMESTAMP NULL DEFAULT NULL,
  FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE,
  INDEX idx_ads_status (status),
  INDEX idx_ads_model_created (model_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ad_answers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ad_id INT NOT NULL,
  question_key VARCHAR(80) NOT NULL,
  question_label VARCHAR(180) NOT NULL,
  answer VARCHAR(20) NOT NULL,
  FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS target_groups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  chat_id BIGINT NOT NULL UNIQUE,
  title VARCHAR(255) NULL,
  type VARCHAR(50) NULL,
  status ENUM('pending','approved','blocked','inactive') NOT NULL DEFAULT 'pending',
  last_error TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_groups_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ad_sends (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ad_id INT NOT NULL,
  group_id INT NOT NULL,
  telegram_message_id BIGINT NULL,
  status ENUM('sent','failed') NOT NULL,
  error TEXT NULL,
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE,
  FOREIGN KEY (group_id) REFERENCES target_groups(id) ON DELETE CASCADE,
  INDEX idx_sends_ad (ad_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
