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

CREATE TABLE shops (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  phone VARCHAR(32) NULL,
  email VARCHAR(190) NULL,
  status ENUM('active','suspended') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('shop_owner','staff') NOT NULL DEFAULT 'staff',
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_users_email (email),
  KEY idx_users_shop (shop_id),
  CONSTRAINT fk_users_shop FOREIGN KEY (shop_id) REFERENCES shops(id)
) ENGINE=InnoDB;

CREATE TABLE customers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(160) NOT NULL,
  phone VARCHAR(32) NOT NULL,
  email VARCHAR(190) NULL,
  address TEXT NULL,
  consent_recorded_at DATETIME NULL,
  created_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_customers_shop_created (shop_id, created_at),
  KEY idx_customers_phone (shop_id, phone),
  CONSTRAINT fk_customers_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
  CONSTRAINT fk_customers_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE devices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  brand VARCHAR(80) NOT NULL,
  model VARCHAR(120) NOT NULL,
  serial_last4 VARCHAR(4) NULL,
  imei_last4 VARCHAR(4) NULL,
  enrollment_type ENUM('not_enrolled','customer_app','approved_enterprise') NOT NULL DEFAULT 'not_enrolled',
  enrollment_status ENUM('not_started','pending_consent','enrolled','unsupported','removed') NOT NULL DEFAULT 'not_started',
  notes VARCHAR(500) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_devices_shop_created (shop_id, created_at),
  KEY idx_devices_customer (customer_id),
  CONSTRAINT fk_devices_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
  CONSTRAINT fk_devices_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

CREATE TABLE emi_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  device_id BIGINT UNSIGNED NULL,
  reference_code VARCHAR(64) NOT NULL,
  principal_amount DECIMAL(12,2) NOT NULL,
  total_payable DECIMAL(12,2) NOT NULL,
  installment_amount DECIMAL(12,2) NOT NULL,
  installment_count SMALLINT UNSIGNED NOT NULL,
  start_date DATE NOT NULL,
  status ENUM('active','completed','cancelled') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_plan_shop_ref (shop_id, reference_code),
  KEY idx_plans_shop_status (shop_id, status),
  CONSTRAINT fk_plans_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
  CONSTRAINT fk_plans_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_plans_device FOREIGN KEY (device_id) REFERENCES devices(id),
  CONSTRAINT fk_plans_creator FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE installments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  installment_no SMALLINT UNSIGNED NOT NULL,
  due_date DATE NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  status ENUM('due','paid','waived') NOT NULL DEFAULT 'due',
  paid_at DATETIME NULL,
  UNIQUE KEY uq_installment_no (plan_id, installment_no),
  KEY idx_installments_shop_due (shop_id, status, due_date),
  CONSTRAINT fk_installments_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
  CONSTRAINT fk_installments_plan FOREIGN KEY (plan_id) REFERENCES emi_plans(id)
) ENGINE=InnoDB;

CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NOT NULL,
  installment_id BIGINT UNSIGNED NULL,
  amount DECIMAL(12,2) NOT NULL,
  method ENUM('cash','upi','bank_transfer','gateway','other') NOT NULL,
  status ENUM('recorded','verified','reversed') NOT NULL DEFAULT 'recorded',
  external_reference VARCHAR(128) NULL,
  received_by BIGINT UNSIGNED NOT NULL,
  received_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_payments_shop_received (shop_id, received_at),
  UNIQUE KEY uq_payment_external_ref (shop_id, external_reference),
  CONSTRAINT fk_payments_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
  CONSTRAINT fk_payments_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_payments_plan FOREIGN KEY (plan_id) REFERENCES emi_plans(id),
  CONSTRAINT fk_payments_installment FOREIGN KEY (installment_id) REFERENCES installments(id),
  CONSTRAINT fk_payments_receiver FOREIGN KEY (received_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  shop_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(80) NOT NULL,
  entity_type VARCHAR(80) NOT NULL,
  entity_id BIGINT UNSIGNED NULL,
  request_id CHAR(36) NULL,
  metadata JSON NULL,
  ip_hash CHAR(64) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_shop_created (shop_id, created_at),
  CONSTRAINT fk_audit_shop FOREIGN KEY (shop_id) REFERENCES shops(id),
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;