USE comptaai_maroc;

CREATE TABLE IF NOT EXISTS subscription_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE,
  slug VARCHAR(120) NOT NULL UNIQUE,
  monthly_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  yearly_price DECIMAL(12,2) NOT NULL DEFAULT 0,
  max_clients INT UNSIGNED NOT NULL DEFAULT 0,
  max_users INT UNSIGNED NOT NULL DEFAULT 0,
  max_documents_per_month INT UNSIGNED NOT NULL DEFAULT 0,
  ai_features_enabled TINYINT(1) NOT NULL DEFAULT 0,
  ocr_limit_per_month INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY subscription_plans_status_index (status)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_id BIGINT UNSIGNED NOT NULL,
  owner_type ENUM('company','client') NOT NULL DEFAULT 'company',
  owner_id BIGINT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL,
  billing_cycle ENUM('monthly','yearly') NOT NULL DEFAULT 'monthly',
  status ENUM('active','trial','expired','cancelled') NOT NULL DEFAULT 'trial',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY subscriptions_plan_index (plan_id),
  KEY subscriptions_owner_index (owner_type, owner_id),
  KEY subscriptions_status_index (status),
  KEY subscriptions_end_date_index (end_date),
  CONSTRAINT subscriptions_plan_fk FOREIGN KEY (plan_id) REFERENCES subscription_plans(id),
  CONSTRAINT subscriptions_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS subscription_usage (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  subscription_id BIGINT UNSIGNED NOT NULL,
  usage_month TINYINT UNSIGNED NOT NULL,
  usage_year SMALLINT UNSIGNED NOT NULL,
  documents_uploaded INT UNSIGNED NOT NULL DEFAULT 0,
  ocr_processed INT UNSIGNED NOT NULL DEFAULT 0,
  ai_requests INT UNSIGNED NOT NULL DEFAULT 0,
  users_count INT UNSIGNED NOT NULL DEFAULT 0,
  clients_count INT UNSIGNED NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY subscription_usage_period_unique (subscription_id, usage_year, usage_month),
  CONSTRAINT subscription_usage_subscription_fk FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT INTO subscription_plans
  (name, slug, monthly_price, yearly_price, max_clients, max_users, max_documents_per_month, ai_features_enabled, ocr_limit_per_month, status)
VALUES
  ('Free Trial', 'free-trial', 0.00, 0.00, 2, 2, 50, 0, 25, 'active'),
  ('Starter', 'starter', 199.00, 1990.00, 5, 3, 300, 0, 100, 'active'),
  ('Pro', 'pro', 499.00, 4990.00, 20, 10, 1500, 1, 600, 'active'),
  ('Cabinet', 'cabinet', 999.00, 9990.00, 100, 30, 6000, 1, 2500, 'active'),
  ('Enterprise', 'enterprise', 0.00, 0.00, 0, 0, 0, 1, 0, 'active')
ON DUPLICATE KEY UPDATE
  monthly_price = VALUES(monthly_price),
  yearly_price = VALUES(yearly_price),
  max_clients = VALUES(max_clients),
  max_users = VALUES(max_users),
  max_documents_per_month = VALUES(max_documents_per_month),
  ai_features_enabled = VALUES(ai_features_enabled),
  ocr_limit_per_month = VALUES(ocr_limit_per_month),
  status = VALUES(status);