-- Dial Odisha Hosting Portal starter schema (MySQL 8 / MariaDB compatible baseline)
-- Review and adapt before importing. Foreign-key policy may need adjustment for your environment.

CREATE TABLE brands (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  primary_domain VARCHAR(255) NULL,
  logo_path VARCHAR(500) NULL,
  status ENUM('draft','active','suspended') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id BIGINT UNSIGNED NULL,
  reseller_parent_id BIGINT UNSIGNED NULL,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('super_admin','brand_admin','partner_reseller','support_staff','customer') NOT NULL,
  status ENUM('pending','active','suspended','closed') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_users_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL,
  CONSTRAINT fk_users_parent FOREIGN KEY (reseller_parent_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_users_brand_role (brand_id, role),
  INDEX idx_users_parent (reseller_parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE hosting_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  brand_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  provider_package VARCHAR(120) NULL,
  storage_mb BIGINT UNSIGNED NULL,
  bandwidth_mb BIGINT UNSIGNED NULL,
  monthly_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  annual_price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status ENUM('draft','active','retired') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_plans_brand FOREIGN KEY (brand_id) REFERENCES brands(id),
  INDEX idx_plans_brand_status (brand_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE hosting_orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_number VARCHAR(40) NOT NULL UNIQUE,
  brand_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  reseller_id BIGINT UNSIGNED NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  domain_name VARCHAR(253) NOT NULL,
  billing_cycle ENUM('monthly','annual') NOT NULL DEFAULT 'annual',
  amount DECIMAL(12,2) NOT NULL,
  payment_status ENUM('pending','paid','failed','refunded','manual_review') NOT NULL DEFAULT 'pending',
  provisioning_status ENUM('not_started','queued','in_progress','active','failed','suspended','terminated') NOT NULL DEFAULT 'not_started',
  provider_account_ref VARCHAR(190) NULL,
  idempotency_key VARCHAR(100) NULL UNIQUE,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_orders_brand FOREIGN KEY (brand_id) REFERENCES brands(id),
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES users(id),
  CONSTRAINT fk_orders_reseller FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_orders_plan FOREIGN KEY (plan_id) REFERENCES hosting_plans(id),
  INDEX idx_orders_customer (customer_id, created_at),
  INDEX idx_orders_brand (brand_id, created_at),
  INDEX idx_orders_provision (provisioning_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payment_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(60) NOT NULL,
  provider_transaction_ref VARCHAR(190) NULL,
  amount DECIMAL(12,2) NOT NULL,
  status ENUM('initiated','pending','verified','failed','refunded','manual_review') NOT NULL DEFAULT 'initiated',
  verified_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES hosting_orders(id),
  UNIQUE KEY uq_payment_provider_ref (provider, provider_transaction_ref),
  INDEX idx_payment_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reseller_commissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reseller_id BIGINT UNSIGNED NOT NULL,
  order_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status ENUM('pending','approved','paid','reversed') NOT NULL DEFAULT 'pending',
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_commission_reseller FOREIGN KEY (reseller_id) REFERENCES users(id),
  CONSTRAINT fk_commission_order FOREIGN KEY (order_id) REFERENCES hosting_orders(id),
  UNIQUE KEY uq_commission_order_reseller (reseller_id, order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_user_id BIGINT UNSIGNED NULL,
  brand_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id VARCHAR(100) NULL,
  outcome ENUM('success','failure','blocked') NOT NULL DEFAULT 'success',
  request_id VARCHAR(100) NULL,
  ip_hash CHAR(64) NULL,
  metadata_json JSON NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_brand_time (brand_id, created_at),
  INDEX idx_audit_action_time (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;