CREATE DATABASE IF NOT EXISTS b2b_courier
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE b2b_courier;

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS saas_license_domains;
DROP TABLE IF EXISTS saas_licenses;
DROP TABLE IF EXISTS saas_payments;
DROP TABLE IF EXISTS saas_subscriptions;
DROP TABLE IF EXISTS saas_plan_modules;
DROP TABLE IF EXISTS saas_tenant_modules;
DROP TABLE IF EXISTS saas_modules;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS saas_tenants;
DROP TABLE IF EXISTS saas_plans;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE saas_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_name VARCHAR(100) NOT NULL,
  plan_code VARCHAR(50) NOT NULL UNIQUE,
  billing_cycle ENUM('monthly','quarterly','yearly','lifetime') NOT NULL DEFAULT 'monthly',
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  max_users INT UNSIGNED NOT NULL DEFAULT 5,
  max_shipments INT UNSIGNED NOT NULL DEFAULT 1000,
  max_clients INT UNSIGNED NOT NULL DEFAULT 50,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE saas_tenants (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_code VARCHAR(40) NOT NULL UNIQUE,
  company_name VARCHAR(200) NOT NULL,
  contact_person VARCHAR(150) NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(25) NULL,
  domain VARCHAR(255) NULL,
  logo VARCHAR(255) NULL,
  timezone VARCHAR(60) NOT NULL DEFAULT 'Asia/Kolkata',
  currency CHAR(3) NOT NULL DEFAULT 'INR',
  status ENUM('pending','active','suspended','expired') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_tenant_status(status)
) ENGINE=InnoDB;

CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NULL,
  role_name VARCHAR(100) NOT NULL,
  role_code VARCHAR(60) NOT NULL,
  tenant_scope BIGINT UNSIGNED GENERATED ALWAYS AS (IFNULL(tenant_id,0)) STORED,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_roles_tenant FOREIGN KEY (tenant_id) REFERENCES saas_tenants(id),
  UNIQUE KEY uq_role_scope_code(tenant_scope,role_code)
) ENGINE=InnoDB;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(25) NULL,
  password_hash VARCHAR(255) NOT NULL,
  status ENUM('active','inactive','blocked') NOT NULL DEFAULT 'active',
  tenant_scope BIGINT UNSIGNED GENERATED ALWAYS AS (IFNULL(tenant_id,0)) STORED,
  last_login_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES saas_tenants(id),
  CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id),
  UNIQUE KEY uq_user_scope_email(tenant_scope,email),
  INDEX idx_users_tenant(tenant_id)
) ENGINE=InnoDB;

CREATE TABLE saas_subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  payment_status ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
  subscription_status ENUM('active','expired','cancelled','suspended') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sub_tenant FOREIGN KEY (tenant_id) REFERENCES saas_tenants(id),
  CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES saas_plans(id),
  INDEX idx_sub_tenant(tenant_id),
  INDEX idx_sub_expiry(end_date)
) ENGINE=InnoDB;

CREATE TABLE saas_licenses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  subscription_id BIGINT UNSIGNED NOT NULL,
  license_key_hash CHAR(64) NOT NULL UNIQUE,
  license_type ENUM('standard','professional','enterprise','lifetime') NOT NULL DEFAULT 'standard',
  issued_at DATETIME NOT NULL,
  expires_at DATETIME NULL,
  last_verified_at DATETIME NULL,
  grace_period_days INT UNSIGNED NOT NULL DEFAULT 7,
  status ENUM('active','expired','revoked','suspended') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_license_tenant FOREIGN KEY (tenant_id) REFERENCES saas_tenants(id),
  CONSTRAINT fk_license_subscription FOREIGN KEY (subscription_id) REFERENCES saas_subscriptions(id),
  INDEX idx_license_tenant(tenant_id),
  INDEX idx_license_status(status)
) ENGINE=InnoDB;

CREATE TABLE saas_license_domains (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  license_id BIGINT UNSIGNED NOT NULL,
  domain_name VARCHAR(255) NOT NULL,
  is_verified TINYINT(1) NOT NULL DEFAULT 0,
  verified_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_domain_license FOREIGN KEY (license_id) REFERENCES saas_licenses(id) ON DELETE CASCADE,
  UNIQUE KEY uq_license_domain(license_id,domain_name)
) ENGINE=InnoDB;

CREATE TABLE saas_modules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  module_name VARCHAR(100) NOT NULL,
  module_code VARCHAR(60) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE saas_plan_modules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  plan_id BIGINT UNSIGNED NOT NULL,
  module_id BIGINT UNSIGNED NOT NULL,
  is_enabled TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_pm_plan FOREIGN KEY (plan_id) REFERENCES saas_plans(id) ON DELETE CASCADE,
  CONSTRAINT fk_pm_module FOREIGN KEY (module_id) REFERENCES saas_modules(id) ON DELETE CASCADE,
  UNIQUE KEY uq_plan_module(plan_id,module_id)
) ENGINE=InnoDB;

CREATE TABLE saas_tenant_modules (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  module_id BIGINT UNSIGNED NOT NULL,
  is_enabled TINYINT(1) NOT NULL DEFAULT 1,
  CONSTRAINT fk_tm_tenant FOREIGN KEY (tenant_id) REFERENCES saas_tenants(id) ON DELETE CASCADE,
  CONSTRAINT fk_tm_module FOREIGN KEY (module_id) REFERENCES saas_modules(id) ON DELETE CASCADE,
  UNIQUE KEY uq_tenant_module(tenant_id,module_id)
) ENGINE=InnoDB;

CREATE TABLE saas_payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NOT NULL,
  subscription_id BIGINT UNSIGNED NOT NULL,
  transaction_reference VARCHAR(150) NULL,
  amount DECIMAL(12,2) NOT NULL,
  payment_method VARCHAR(50) NULL,
  payment_status ENUM('pending','success','failed','refunded') NOT NULL DEFAULT 'pending',
  paid_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_tenant FOREIGN KEY (tenant_id) REFERENCES saas_tenants(id),
  CONSTRAINT fk_payment_subscription FOREIGN KEY (subscription_id) REFERENCES saas_subscriptions(id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  description TEXT NULL,
  ip_address VARCHAR(45) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_tenant(tenant_id),
  INDEX idx_audit_user(user_id),
  INDEX idx_audit_date(created_at)
) ENGINE=InnoDB;
