-- Core SaaS schema for multi-tenant control
CREATE TABLE IF NOT EXISTS tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  business_name VARCHAR(150) NOT NULL,
  slug VARCHAR(80) NOT NULL UNIQUE,
  owner_email VARCHAR(190) NOT NULL,
  referral_code VARCHAR(32) UNIQUE,
  referred_by_tenant_id INT NULL,
  referrer_bonus_granted TINYINT(1) DEFAULT 0,
  db_name VARCHAR(128) NOT NULL,
  theme VARCHAR(40) DEFAULT 'classic',
  primary_color VARCHAR(16) DEFAULT '#6c5ce7',
  stripe_customer_id VARCHAR(120),
  status ENUM('trial','active','past_due','canceled') DEFAULT 'trial',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tenants_referrer FOREIGN KEY (referred_by_tenant_id)
    REFERENCES tenants(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS subscriptions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  plan VARCHAR(40) NOT NULL,
  stripe_subscription_id VARCHAR(140),
  current_period_end INT,
  status VARCHAR(40),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tenant_domains (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  domain VARCHAR(190) NOT NULL,
  is_primary TINYINT(1) DEFAULT 1,
  is_verified TINYINT(1) DEFAULT 0,
  verification_token VARCHAR(64),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_domain (domain),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
