-- =============================================================================
-- D2C Dashboard / Billr — Full MySQL Schema
-- Supports all React dashboard modules + existing PHP webservices
--
-- Modules covered:
--   • Home / Dashboard widgets
--   • User Management (KYC, login history, suspend/ban)
--   • Transactions (recharge, bills, refunds, flags)
--   • Customer Support (tickets + history)
--   • Service Status (API health monitoring)
--   • Billing & Commission
--   • RBAC (staff roles & permissions)
--   • Analytics & Reports (via views + snapshots)
--   • Audit Logs
--   • Settings
--   • Auth / Profile / Referral (existing PHP services)
--
-- Usage (fresh install — drops existing tables):
--   mysql -u root -p < schema.sql
-- =============================================================================

CREATE DATABASE IF NOT EXISTS driftdevelopers_biller_datatable
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE driftdevelopers_biller_datatable;




-- =============================================================================
-- SECTION 1: END-USERS (mobile app / Billr clients)
-- Module: Auth, User Management, Profile
-- PHP: login_service, signup_service, forget_service, get_profile, reffer_*
-- =============================================================================

CREATE TABLE client_management (
  client_user_id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_username             VARCHAR(50)  NOT NULL,
  client_password             VARCHAR(255) NOT NULL,
  client_full_name            VARCHAR(100) NOT NULL,
  client_phone                VARCHAR(20)  NOT NULL,
  client_email                VARCHAR(100) DEFAULT NULL,
  client_address              VARCHAR(255) DEFAULT NULL,
  client_city                 VARCHAR(100) DEFAULT NULL,
  client_state                VARCHAR(100) DEFAULT NULL,
  client_status               ENUM('active','suspended','banned','inactive') NOT NULL DEFAULT 'active',
  client_kyc_status           ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  client_wallet_status        ENUM('inactive','active','blocked') NOT NULL DEFAULT 'inactive',
  client_bank_name            VARCHAR(100) DEFAULT NULL,
  client_bank_account         VARCHAR(50)  DEFAULT NULL,
  client_last_login_at        DATETIME     DEFAULT NULL,
  client_last_device          VARCHAR(100) DEFAULT NULL,
  client_last_activity        VARCHAR(255) DEFAULT NULL COMMENT 'Human-readable activity summary for dashboard',
  client_reset_token          VARCHAR(64)  DEFAULT NULL,
  client_reset_token_expiry   DATETIME     DEFAULT NULL,
  created_at                  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at                  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (client_user_id),
  UNIQUE KEY uq_client_phone (client_phone),
  UNIQUE KEY uq_client_username (client_username),
  KEY idx_client_email (client_email),
  KEY idx_client_status (client_status),
  KEY idx_client_kyc_status (client_kyc_status),
  KEY idx_client_reset_token (client_reset_token),
  KEY idx_client_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='End-user accounts (mobile app customers)';

CREATE TABLE client_login_history (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_user_id    INT UNSIGNED NOT NULL,
  login_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip_address        VARCHAR(45)  DEFAULT NULL,
  device_name       VARCHAR(100) DEFAULT NULL,
  user_agent        VARCHAR(255) DEFAULT NULL,
  login_status      ENUM('success','failed') NOT NULL DEFAULT 'success',
  PRIMARY KEY (id),
  KEY idx_clh_client_user (client_user_id),
  KEY idx_clh_login_at (login_at),
  CONSTRAINT fk_clh_client
    FOREIGN KEY (client_user_id) REFERENCES client_management (client_user_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='User Management — login history per client';

CREATE TABLE client_tokens (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_user_id    INT UNSIGNED NOT NULL,
  token             VARCHAR(64)  NOT NULL,
  expires_at        DATETIME     NOT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_client_tokens_token (token),
  KEY idx_client_tokens_user (client_user_id),
  KEY idx_client_tokens_expires (expires_at),
  CONSTRAINT fk_client_tokens_user
    FOREIGN KEY (client_user_id) REFERENCES client_management (client_user_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Persisted auth tokens for mobile clients';

CREATE TABLE client_kyc_documents (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_user_id    INT UNSIGNED NOT NULL,
  document_type     ENUM('aadhaar','pan','passport','driving_license','other') NOT NULL,
  document_number   VARCHAR(50)  DEFAULT NULL,
  file_path         VARCHAR(500) DEFAULT NULL,
  verification_status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  verified_by       INT UNSIGNED DEFAULT NULL COMMENT 'staff_users.staff_id',
  verified_at       DATETIME     DEFAULT NULL,
  rejection_reason  VARCHAR(255) DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_kyc_client (client_user_id),
  KEY idx_kyc_status (verification_status),
  CONSTRAINT fk_kyc_client
    FOREIGN KEY (client_user_id) REFERENCES client_management (client_user_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='User Management — KYC document uploads and verification';

CREATE TABLE password_reset_tokens (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ps_client_id      INT UNSIGNED NOT NULL,
  ps_token          VARCHAR(64)  NOT NULL,
  ps_expiry         DATETIME     NOT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_ps_client_id (ps_client_id),
  KEY idx_ps_token (ps_token),
  CONSTRAINT fk_reset_client
    FOREIGN KEY (ps_client_id) REFERENCES client_management (client_user_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE reffer_code (
  reffer_id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  reffer_user_id      INT UNSIGNED NOT NULL,
  reffer_user_code    VARCHAR(20)  NOT NULL,
  is_used             TINYINT(1)   NOT NULL DEFAULT 0,
  used_by_user_id     INT UNSIGNED DEFAULT NULL,
  used_at             DATETIME     DEFAULT NULL,
  created_at          TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (reffer_id),
  UNIQUE KEY uq_reffer_user_code (reffer_user_code),
  KEY idx_reffer_user_id (reffer_user_id),
  CONSTRAINT fk_reffer_owner
    FOREIGN KEY (reffer_user_id) REFERENCES client_management (client_user_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_reffer_used_by
    FOREIGN KEY (used_by_user_id) REFERENCES client_management (client_user_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Legacy tables referenced by utils/auth_utils.php
CREATE TABLE users (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name              VARCHAR(100) NOT NULL,
  email             VARCHAR(100) NOT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_tokens (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id           INT UNSIGNED NOT NULL,
  token             VARCHAR(64)  NOT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at        DATETIME     NOT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_tokens_token (token),
  KEY idx_user_tokens_user_id (user_id),
  CONSTRAINT fk_tokens_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- SECTION 2: STAFF & RBAC
-- Module: RBAC, Audit Logs (staff actions), Settings (admin)
-- =============================================================================

CREATE TABLE staff_users (
  staff_id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  staff_code        VARCHAR(20)  NOT NULL COMMENT 'e.g. Agent001',
  full_name         VARCHAR(100) NOT NULL,
  email             VARCHAR(100) NOT NULL,
  password_hash     VARCHAR(255) NOT NULL,
  phone             VARCHAR(20)  DEFAULT NULL,
  staff_type        ENUM('admin','support_agent','finance','partner','agent') NOT NULL DEFAULT 'support_agent',
  status            ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  last_login_at     DATETIME     DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (staff_id),
  UNIQUE KEY uq_staff_code (staff_code),
  UNIQUE KEY uq_staff_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Dashboard staff / agents / partners';

CREATE TABLE roles (
  role_id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_name         VARCHAR(50)  NOT NULL,
  description       VARCHAR(255) DEFAULT NULL,
  is_system_role    TINYINT(1)   NOT NULL DEFAULT 0,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (role_id),
  UNIQUE KEY uq_role_name (role_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='RBAC — role definitions';

CREATE TABLE permissions (
  permission_id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  permission_key    VARCHAR(50)  NOT NULL COMMENT 'e.g. users, transactions, billing',
  permission_label  VARCHAR(100) NOT NULL,
  module_name       VARCHAR(50)  NOT NULL COMMENT 'Dashboard sidebar module',
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (permission_id),
  UNIQUE KEY uq_permission_key (permission_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='RBAC — granular permissions';

CREATE TABLE role_permissions (
  role_id           INT UNSIGNED NOT NULL,
  permission_id     INT UNSIGNED NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_role
    FOREIGN KEY (role_id) REFERENCES roles (role_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_rp_permission
    FOREIGN KEY (permission_id) REFERENCES permissions (permission_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE staff_roles (
  staff_id          INT UNSIGNED NOT NULL,
  role_id           INT UNSIGNED NOT NULL,
  assigned_at       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (staff_id, role_id),
  CONSTRAINT fk_sr_staff
    FOREIGN KEY (staff_id) REFERENCES staff_users (staff_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_sr_role
    FOREIGN KEY (role_id) REFERENCES roles (role_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE staff_tokens (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  staff_id          INT UNSIGNED NOT NULL,
  token             VARCHAR(64)  NOT NULL,
  expires_at        DATETIME     NOT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_staff_tokens_token (token),
  KEY idx_staff_tokens_staff (staff_id),
  CONSTRAINT fk_staff_tokens_staff
    FOREIGN KEY (staff_id) REFERENCES staff_users (staff_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Dashboard staff session tokens';

CREATE TABLE staff_activity_logs (
  id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
  staff_id          INT UNSIGNED NOT NULL,
  action            VARCHAR(255) NOT NULL,
  entity_type       VARCHAR(50)  DEFAULT NULL COMMENT 'client, transaction, ticket, etc.',
  entity_id         VARCHAR(50)  DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_sal_staff (staff_id),
  KEY idx_sal_created (created_at),
  CONSTRAINT fk_sal_staff
    FOREIGN KEY (staff_id) REFERENCES staff_users (staff_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='RBAC — staff activity feed';

-- =============================================================================
-- SECTION 3: SERVICES, GATEWAYS & TRANSACTIONS
-- Modules: Transactions, Dashboard, Analytics, Billing
-- =============================================================================

CREATE TABLE service_categories (
  category_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_code     VARCHAR(30)  NOT NULL COMMENT 'recharge, electricity, dth, etc.',
  category_name     VARCHAR(100) NOT NULL,
  description       VARCHAR(255) DEFAULT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  sort_order        INT          NOT NULL DEFAULT 0,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (category_id),
  UNIQUE KEY uq_category_code (category_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Bill payment service types';

CREATE TABLE payment_gateways (
  gateway_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  gateway_code      VARCHAR(30)  NOT NULL,
  gateway_name      VARCHAR(100) NOT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  config_json       JSON         DEFAULT NULL COMMENT 'API keys, endpoints (encrypted in prod)',
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (gateway_id),
  UNIQUE KEY uq_gateway_code (gateway_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE transactions (
  transaction_id    INT UNSIGNED NOT NULL AUTO_INCREMENT,
  txn_ref           VARCHAR(30)  NOT NULL COMMENT 'Display ID e.g. TXN001',
  client_user_id    INT UNSIGNED NOT NULL,
  category_id       INT UNSIGNED NOT NULL,
  gateway_id        INT UNSIGNED DEFAULT NULL,
  amount            DECIMAL(12,2) NOT NULL,
  currency          CHAR(3)      NOT NULL DEFAULT 'INR',
  service_charge    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  convenience_fee   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  commission_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status            ENUM('pending','success','failed','completed','refunded','cancelled') NOT NULL DEFAULT 'pending',
  failure_reason    VARCHAR(255) DEFAULT NULL,
  is_flagged        TINYINT(1)   NOT NULL DEFAULT 0,
  flagged_reason    VARCHAR(255) DEFAULT NULL,
  flagged_by        INT UNSIGNED DEFAULT NULL COMMENT 'staff_users.staff_id',
  external_ref      VARCHAR(100) DEFAULT NULL COMMENT 'Gateway / provider reference',
  beneficiary_ref   VARCHAR(100) DEFAULT NULL COMMENT 'Mobile number, consumer ID, etc.',
  operator_name     VARCHAR(50)  DEFAULT NULL COMMENT 'Airtel, BSES, etc.',
  metadata_json     JSON         DEFAULT NULL,
  txn_date          DATE         NOT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (transaction_id),
  UNIQUE KEY uq_txn_ref (txn_ref),
  KEY idx_txn_client (client_user_id),
  KEY idx_txn_category (category_id),
  KEY idx_txn_gateway (gateway_id),
  KEY idx_txn_status (status),
  KEY idx_txn_date (txn_date),
  KEY idx_txn_flagged (is_flagged),
  KEY idx_txn_created (created_at),
  CONSTRAINT fk_txn_client
    FOREIGN KEY (client_user_id) REFERENCES client_management (client_user_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_txn_category
    FOREIGN KEY (category_id) REFERENCES service_categories (category_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_txn_gateway
    FOREIGN KEY (gateway_id) REFERENCES payment_gateways (gateway_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_txn_flagged_by
    FOREIGN KEY (flagged_by) REFERENCES staff_users (staff_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='All bill payments and recharges';

CREATE TABLE transaction_actions (
  action_id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  transaction_id    INT UNSIGNED NOT NULL,
  action_type       ENUM('refund','reprocess','flag','unflag','note') NOT NULL,
  performed_by      INT UNSIGNED DEFAULT NULL COMMENT 'staff_users.staff_id',
  notes             VARCHAR(500) DEFAULT NULL,
  amount            DECIMAL(12,2) DEFAULT NULL COMMENT 'Refund amount if applicable',
  status            ENUM('pending','completed','failed') NOT NULL DEFAULT 'pending',
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (action_id),
  KEY idx_ta_transaction (transaction_id),
  KEY idx_ta_type (action_type),
  CONSTRAINT fk_ta_transaction
    FOREIGN KEY (transaction_id) REFERENCES transactions (transaction_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_ta_staff
    FOREIGN KEY (performed_by) REFERENCES staff_users (staff_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Transactions module — refund/reprocess/flag audit trail';

-- =============================================================================
-- SECTION 4: BILLING & COMMISSION
-- Module: Billing & Commission
-- =============================================================================

CREATE TABLE service_commission_rules (
  rule_id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id       INT UNSIGNED NOT NULL,
  service_charge    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  convenience_fee   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  commission_rate   DECIMAL(5,2)  NOT NULL DEFAULT 0.00 COMMENT 'Percentage',
  flat_commission   DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Fixed commission in INR',
  effective_from    DATE         NOT NULL,
  effective_to      DATE         DEFAULT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (rule_id),
  KEY idx_scr_category (category_id),
  KEY idx_scr_active (is_active),
  CONSTRAINT fk_scr_category
    FOREIGN KEY (category_id) REFERENCES service_categories (category_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Per-service charge, fee and commission configuration';

CREATE TABLE financial_adjustments (
  adjustment_id     INT UNSIGNED NOT NULL AUTO_INCREMENT,
  transaction_id    INT UNSIGNED DEFAULT NULL,
  client_user_id    INT UNSIGNED DEFAULT NULL,
  adjustment_type   ENUM('refund','adjustment','reversal') NOT NULL,
  amount            DECIMAL(12,2) NOT NULL,
  reason            VARCHAR(255) DEFAULT NULL,
  processed_by      INT UNSIGNED DEFAULT NULL COMMENT 'staff_users.staff_id',
  adjustment_date   DATE         NOT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (adjustment_id),
  KEY idx_fa_transaction (transaction_id),
  KEY idx_fa_client (client_user_id),
  KEY idx_fa_type (adjustment_type),
  KEY idx_fa_date (adjustment_date),
  CONSTRAINT fk_fa_transaction
    FOREIGN KEY (transaction_id) REFERENCES transactions (transaction_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_fa_client
    FOREIGN KEY (client_user_id) REFERENCES client_management (client_user_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_fa_staff
    FOREIGN KEY (processed_by) REFERENCES staff_users (staff_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Refunds, adjustments and reversals ledger';

CREATE TABLE agent_commission_ledger (
  ledger_id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  staff_id          INT UNSIGNED NOT NULL COMMENT 'Agent or partner',
  category_id       INT UNSIGNED NOT NULL,
  transaction_id    INT UNSIGNED DEFAULT NULL,
  commission_amount DECIMAL(12,2) NOT NULL,
  period_month      CHAR(7)      DEFAULT NULL COMMENT 'YYYY-MM for monthly rollup',
  notes             VARCHAR(255) DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (ledger_id),
  KEY idx_acl_staff (staff_id),
  KEY idx_acl_category (category_id),
  KEY idx_acl_period (period_month),
  CONSTRAINT fk_acl_staff
    FOREIGN KEY (staff_id) REFERENCES staff_users (staff_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_acl_category
    FOREIGN KEY (category_id) REFERENCES service_categories (category_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_acl_transaction
    FOREIGN KEY (transaction_id) REFERENCES transactions (transaction_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Agent/partner commission earnings';

-- =============================================================================
-- SECTION 5: CUSTOMER SUPPORT
-- Module: Customer Support
-- =============================================================================

CREATE TABLE support_tickets (
  ticket_id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ticket_ref        VARCHAR(20)  NOT NULL COMMENT 'e.g. TCKT1001',
  client_user_id    INT UNSIGNED NOT NULL,
  subject           VARCHAR(255) NOT NULL,
  description       TEXT         DEFAULT NULL,
  status            ENUM('open','in_progress','closed','escalated') NOT NULL DEFAULT 'open',
  priority          ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  assigned_to       INT UNSIGNED DEFAULT NULL COMMENT 'staff_users.staff_id',
  closed_at         DATETIME     DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (ticket_id),
  UNIQUE KEY uq_ticket_ref (ticket_ref),
  KEY idx_st_client (client_user_id),
  KEY idx_st_status (status),
  KEY idx_st_assigned (assigned_to),
  CONSTRAINT fk_st_client
    FOREIGN KEY (client_user_id) REFERENCES client_management (client_user_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_st_assigned
    FOREIGN KEY (assigned_to) REFERENCES staff_users (staff_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE support_ticket_history (
  history_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ticket_id         INT UNSIGNED NOT NULL,
  staff_id          INT UNSIGNED DEFAULT NULL,
  action            VARCHAR(255) NOT NULL,
  old_status        VARCHAR(20)  DEFAULT NULL,
  new_status        VARCHAR(20)  DEFAULT NULL,
  message           TEXT         DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (history_id),
  KEY idx_sth_ticket (ticket_id),
  CONSTRAINT fk_sth_ticket
    FOREIGN KEY (ticket_id) REFERENCES support_tickets (ticket_id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT fk_sth_staff
    FOREIGN KEY (staff_id) REFERENCES staff_users (staff_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Ticket timeline / history';

-- =============================================================================
-- SECTION 6: SERVICE STATUS MONITORING
-- Module: Service Status Dashboard
-- =============================================================================

CREATE TABLE external_services (
  service_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  service_code      VARCHAR(50)  NOT NULL,
  service_name      VARCHAR(100) NOT NULL,
  endpoint_url      VARCHAR(500) DEFAULT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  check_interval_sec INT         NOT NULL DEFAULT 60,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (service_id),
  UNIQUE KEY uq_service_code (service_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='External APIs to monitor (recharge, electricity, DTH, etc.)';

CREATE TABLE service_health_checks (
  check_id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  service_id        INT UNSIGNED NOT NULL,
  status            ENUM('online','degraded','offline') NOT NULL,
  latency_ms        INT UNSIGNED DEFAULT NULL,
  error_message     VARCHAR(500) DEFAULT NULL,
  checked_at        DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (check_id),
  KEY idx_shc_service (service_id),
  KEY idx_shc_checked (checked_at),
  KEY idx_shc_status (status),
  CONSTRAINT fk_shc_service
    FOREIGN KEY (service_id) REFERENCES external_services (service_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Health check history for service status dashboard';

-- =============================================================================
-- SECTION 7: AUDIT, ALERTS, SETTINGS, ANALYTICS
-- Modules: Audit Logs, Settings, Dashboard Alerts, Analytics, Reports
-- =============================================================================

CREATE TABLE audit_logs (
  log_id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  log_type          ENUM('admin_action','user_activity','api_call','login_attempt','change_history','system') NOT NULL,
  detail            VARCHAR(500) NOT NULL,
  ip_address        VARCHAR(45)  DEFAULT NULL,
  actor_type        ENUM('staff','client','system') DEFAULT NULL,
  actor_id          INT UNSIGNED DEFAULT NULL,
  entity_type       VARCHAR(50)  DEFAULT NULL,
  entity_id         VARCHAR(50)  DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (log_id),
  KEY idx_audit_type (log_type),
  KEY idx_audit_created (created_at),
  KEY idx_audit_actor (actor_type, actor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Audit & Logs module';

CREATE TABLE app_settings (
  setting_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_key       VARCHAR(100) NOT NULL,
  setting_value     TEXT         NOT NULL,
  setting_group     VARCHAR(50)  NOT NULL DEFAULT 'general' COMMENT 'general, billing, support, notifications',
  description       VARCHAR(255) DEFAULT NULL,
  updated_by        INT UNSIGNED DEFAULT NULL,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (setting_id),
  UNIQUE KEY uq_setting_key (setting_key),
  CONSTRAINT fk_settings_staff
    FOREIGN KEY (updated_by) REFERENCES staff_users (staff_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Settings module — key/value configuration';

CREATE TABLE support_response_templates (
  template_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  template_name     VARCHAR(100) NOT NULL,
  subject           VARCHAR(255) DEFAULT NULL,
  body              TEXT         NOT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  created_by        INT UNSIGNED DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (template_id),
  CONSTRAINT fk_srt_staff
    FOREIGN KEY (created_by) REFERENCES staff_users (staff_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Settings / Support — canned response templates';

CREATE TABLE dashboard_alerts (
  alert_id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  alert_type        ENUM('service_down','failed_txn_spike','kyc_pending','security','general') NOT NULL,
  title             VARCHAR(150) NOT NULL,
  message           VARCHAR(500) NOT NULL,
  severity          ENUM('info','warning','critical') NOT NULL DEFAULT 'warning',
  is_read           TINYINT(1)   NOT NULL DEFAULT 0,
  is_resolved       TINYINT(1)   NOT NULL DEFAULT 0,
  related_entity    VARCHAR(50)  DEFAULT NULL,
  related_id        VARCHAR(50)  DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at       DATETIME     DEFAULT NULL,
  PRIMARY KEY (alert_id),
  KEY idx_da_severity (severity),
  KEY idx_da_resolved (is_resolved),
  KEY idx_da_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Dashboard home — pending alerts widget';

CREATE TABLE analytics_daily_snapshots (
  snapshot_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  snapshot_date     DATE         NOT NULL,
  total_revenue     DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  total_transactions INT UNSIGNED NOT NULL DEFAULT 0,
  successful_txns   INT UNSIGNED NOT NULL DEFAULT 0,
  failed_txns       INT UNSIGNED NOT NULL DEFAULT 0,
  new_users         INT UNSIGNED NOT NULL DEFAULT 0,
  retained_users    INT UNSIGNED NOT NULL DEFAULT 0,
  avg_ticket_size   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  onboarded_users   INT UNSIGNED NOT NULL DEFAULT 0,
  paid_users        INT UNSIGNED NOT NULL DEFAULT 0,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (snapshot_id),
  UNIQUE KEY uq_snapshot_date (snapshot_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='Analytics module — pre-aggregated daily metrics for charts';

-- =============================================================================
-- SECTION 8: VIEWS (Dashboard, Analytics, Reports)
-- =============================================================================

CREATE OR REPLACE VIEW v_dashboard_summary AS
SELECT
  (SELECT COUNT(*) FROM client_management WHERE client_status = 'active') AS active_users,
  (SELECT COALESCE(SUM(amount), 0) FROM transactions
   WHERE status IN ('success','completed')
     AND txn_date >= DATE_FORMAT(CURDATE(), '%Y-%m-01')) AS monthly_revenue,
  (SELECT COUNT(*) FROM dashboard_alerts WHERE is_resolved = 0) AS pending_alerts,
  (SELECT COUNT(*) FROM transactions WHERE status = 'pending') AS pending_transactions,
  (SELECT COUNT(*) FROM support_tickets WHERE status IN ('open','in_progress')) AS open_tickets;

CREATE OR REPLACE VIEW v_recent_transactions AS
SELECT
  t.txn_ref,
  c.client_full_name AS user_name,
  t.amount,
  t.currency,
  t.status,
  sc.category_name AS service_type,
  pg.gateway_name,
  t.txn_date,
  t.created_at
FROM transactions t
JOIN client_management c ON c.client_user_id = t.client_user_id
JOIN service_categories sc ON sc.category_id = t.category_id
LEFT JOIN payment_gateways pg ON pg.gateway_id = t.gateway_id
ORDER BY t.created_at DESC
LIMIT 50;

CREATE OR REPLACE VIEW v_daily_revenue AS
SELECT
  txn_date AS period_date,
  DAYNAME(txn_date) AS period_label,
  SUM(CASE WHEN status IN ('success','completed') THEN amount ELSE 0 END) AS revenue,
  COUNT(*) AS txn_count
FROM transactions
GROUP BY txn_date
ORDER BY txn_date DESC;

CREATE OR REPLACE VIEW v_transactions_by_category AS
SELECT
  sc.category_name AS service_type,
  COUNT(t.transaction_id) AS txn_count,
  SUM(CASE WHEN t.status IN ('success','completed') THEN t.amount ELSE 0 END) AS total_amount
FROM service_categories sc
LEFT JOIN transactions t ON t.category_id = sc.category_id
GROUP BY sc.category_id, sc.category_name
ORDER BY txn_count DESC;

CREATE OR REPLACE VIEW v_service_status_latest AS
SELECT
  es.service_id,
  es.service_name,
  shc.status,
  shc.latency_ms,
  shc.checked_at AS last_checked
FROM external_services es
LEFT JOIN service_health_checks shc ON shc.check_id = (
  SELECT MAX(check_id) FROM service_health_checks
  WHERE service_id = es.service_id
)
WHERE es.is_active = 1;

CREATE OR REPLACE VIEW v_user_management_list AS
SELECT
  c.client_user_id AS id,
  c.client_full_name AS name,
  c.client_email AS email,
  c.client_phone AS mobile,
  c.client_kyc_status AS kyc_status,
  c.client_last_activity AS activity,
  c.client_status AS status,
  c.client_last_device AS device,
  c.client_last_login_at,
  c.created_at
FROM client_management c;

CREATE OR REPLACE VIEW v_billing_commission_summary AS
SELECT
  sc.category_name AS service,
  scr.service_charge AS charge,
  scr.convenience_fee AS fee,
  scr.flat_commission AS commission
FROM service_commission_rules scr
JOIN service_categories sc ON sc.category_id = scr.category_id
WHERE scr.is_active = 1
  AND scr.effective_from <= CURDATE()
  AND (scr.effective_to IS NULL OR scr.effective_to >= CURDATE());

-- =============================================================================
-- SECTION 9: SEED DATA
-- =============================================================================

-- End users (mobile app)
INSERT INTO client_management (
  client_username, client_password, client_full_name, client_phone, client_email,
  client_status, client_kyc_status, client_last_device, client_last_activity, client_last_login_at
) VALUES
  ('1234567890', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
   'John Doe', '1234567890', 'john@example.com', 'active', 'verified', 'iPhone 12',
   'Logged in 2h ago', NOW() - INTERVAL 2 HOUR),
  ('9876543210', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
   'Jane Smith', '9876543210', 'jane@example.com', 'suspended', 'pending', 'Pixel 6',
   'Signed up 1d ago', NOW() - INTERVAL 1 DAY),
  ('5555555555', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
   'Mark Lee', '5555555555', 'mark@example.com', 'active', 'verified', 'Samsung S23',
   'Paid electricity bill', NOW() - INTERVAL 3 HOUR);

INSERT INTO client_login_history (client_user_id, login_at, device_name, ip_address) VALUES
  (1, '2025-06-01 10:00:00', 'iPhone 12', '192.168.1.10'),
  (1, '2025-06-02 15:30:00', 'iPhone 12', '192.168.1.10'),
  (2, '2025-05-30 08:00:00', 'Pixel 6', '172.16.0.22');

-- Staff & RBAC
INSERT INTO staff_users (staff_code, full_name, email, password_hash, staff_type) VALUES
  ('ADMIN001', 'John Admin', 'admin@billr.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin'),
  ('Agent001', 'Anita Support', 'anita@billr.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'support_agent'),
  ('Agent002', 'Raj Finance', 'raj@billr.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'finance'),
  ('PARTNER_A', 'Agent A', 'agenta@billr.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'agent'),
  ('PARTNER_B', 'Agent B', 'agentb@billr.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'agent'),
  ('PARTNER_X', 'Partner X', 'partnerx@billr.local', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'partner');

INSERT INTO permissions (permission_key, permission_label, module_name) VALUES
  ('users', 'Users', 'User Management'),
  ('transactions', 'Transactions', 'Transactions'),
  ('billing', 'Billing', 'Billing & Commission'),
  ('support', 'Support', 'Customer Support'),
  ('analytics', 'Analytics', 'Analytics'),
  ('reports', 'Reports', 'Reports'),
  ('settings', 'Settings', 'Settings'),
  ('rbac', 'RBAC', 'RBAC'),
  ('audit', 'Audit Logs', 'Audit Logs'),
  ('service_status', 'Service Status', 'Service Status');

INSERT INTO roles (role_name, description, is_system_role) VALUES
  ('Admin', 'Full dashboard access', 1),
  ('Support Agent', 'Support and user lookup', 1),
  ('Finance', 'Billing and transactions', 1);

INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, permission_id FROM permissions;

INSERT INTO role_permissions (role_id, permission_id)
SELECT 2, permission_id FROM permissions WHERE permission_key IN ('support','users');

INSERT INTO role_permissions (role_id, permission_id)
SELECT 3, permission_id FROM permissions WHERE permission_key IN ('billing','transactions');

INSERT INTO staff_roles (staff_id, role_id) VALUES (1,1), (2,2), (3,3);

INSERT INTO staff_activity_logs (staff_id, action, entity_type, entity_id) VALUES
  (1, 'Modified user KYC', 'client', '2'),
  (2, 'Replied to support ticket TCKT1002', 'ticket', '2'),
  (3, 'Issued refund for txn TXN002', 'transaction', '2');

-- Service categories & gateways
INSERT INTO service_categories (category_code, category_name, sort_order) VALUES
  ('recharge', 'Recharge', 1),
  ('electricity', 'Electricity Bill', 2),
  ('credit_card', 'Credit Card Payment', 3),
  ('dth', 'DTH', 4),
  ('water', 'Water Service', 5),
  ('internet', 'Internet Service', 6);

INSERT INTO payment_gateways (gateway_code, gateway_name) VALUES
  ('razorpay', 'Razorpay'),
  ('paytm', 'Paytm');

INSERT INTO external_services (service_code, service_name) VALUES
  ('electricity_api', 'Electricity Payment API'),
  ('recharge_api', 'Recharge API'),
  ('credit_card_api', 'Credit Card Bill API'),
  ('dth_api', 'DTH Service API'),
  ('internet_api', 'Internet Service API'),
  ('water_api', 'Water Service API');

INSERT INTO service_health_checks (service_id, status, latency_ms, checked_at) VALUES
  (1, 'online', 120, NOW()),
  (2, 'online', 85, NOW()),
  (3, 'degraded', 210, NOW()),
  (4, 'online', 95, NOW()),
  (5, 'online', 110, NOW()),
  (6, 'offline', NULL, NOW());

-- Commission rules
INSERT INTO service_commission_rules (category_id, service_charge, convenience_fee, flat_commission, effective_from) VALUES
  (2, 10.00, 2.00, 5.00, '2025-01-01'),
  (1, 20.00, 1.50, 8.00, '2025-01-01'),
  (3, 30.00, 3.00, 10.00, '2025-01-01'),
  (4, 15.00, 2.00, 4.00, '2025-01-01');

-- Transactions
INSERT INTO transactions (
  txn_ref, client_user_id, category_id, gateway_id, amount, status,
  failure_reason, is_flagged, txn_date, operator_name, beneficiary_ref
) VALUES
  ('TXN001', 1, 1, 1, 199.00, 'success', NULL, 0, '2025-06-02', 'Airtel', '9876543210'),
  ('TXN002', 2, 2, 2, 520.00, 'failed', 'Insufficient balance', 1, '2025-06-01', 'BSES', 'CON12345'),
  ('TXN003', 3, 2, 1, 250.00, 'completed', NULL, 0, CURDATE(), 'Tata Power', 'CON67890'),
  ('TXN004', 1, 4, 1, 180.00, 'pending', NULL, 0, CURDATE(), 'Tata Sky', '1234567890');

INSERT INTO transaction_actions (transaction_id, action_type, performed_by, notes, status) VALUES
  (2, 'flag', 1, 'Flagged for manual review', 'completed');

INSERT INTO financial_adjustments (transaction_id, adjustment_type, amount, adjustment_date, processed_by) VALUES
  (2, 'refund', 150.00, '2025-06-01', 3),
  (NULL, 'adjustment', 75.00, '2025-06-02', 3),
  (NULL, 'reversal', 50.00, '2025-06-03', 3);

INSERT INTO agent_commission_ledger (staff_id, category_id, commission_amount, period_month) VALUES
  (4, 1, 500.00, '2025-06'),
  (5, 2, 300.00, '2025-06'),
  (6, 4, 200.00, '2025-06');

-- Support tickets
INSERT INTO support_tickets (ticket_ref, client_user_id, subject, status, assigned_to) VALUES
  ('TCKT1001', 1, 'Recharge failed', 'open', NULL),
  ('TCKT1002', 2, 'Unable to login', 'in_progress', 2);

INSERT INTO support_ticket_history (ticket_id, action, message) VALUES
  (1, 'Ticket created', 'Ticket created by user'),
  (2, 'Assigned to agent', 'Assigned to Agent001');

-- Audit logs
INSERT INTO audit_logs (log_type, detail, ip_address, actor_type, actor_id) VALUES
  ('admin_action', 'Updated commission settings', '192.168.1.10', 'staff', 1),
  ('user_activity', 'User login - user123', '172.16.0.22', 'client', 1),
  ('api_call', 'Recharge API - Success', NULL, 'system', NULL),
  ('login_attempt', 'Failed login - user321', '203.0.113.5', 'client', NULL),
  ('change_history', 'Edited support response template', '192.168.1.15', 'staff', 2);

-- Settings
INSERT INTO app_settings (setting_key, setting_value, setting_group, description) VALUES
  ('app_name', 'Billr D2C Dashboard', 'general', 'Application display name'),
  ('country', 'India', 'general', 'Operating country'),
  ('country_code', 'IN', 'general', 'ISO country code'),
  ('locale', 'en-IN', 'general', 'Locale for number and date formatting'),
  ('timezone', 'Asia/Kolkata', 'general', 'Indian Standard Time (IST)'),
  ('date_format', 'DD/MM/YYYY', 'general', 'Date display format (India)'),
  ('default_currency', 'INR', 'billing', 'Default currency code'),
  ('currency_code', 'INR', 'billing', 'ISO 4217 currency code'),
  ('currency_symbol', '₹', 'billing', 'Indian Rupee symbol'),
  ('currency_name', 'Indian Rupee', 'billing', 'Full currency name'),
  ('amount_decimal_places', '2', 'billing', 'Decimal places for rupee amounts'),
  ('amount_thousand_separator', ',', 'billing', 'Indian numbering separator'),
  ('support_email', 'support@billr.local', 'support', 'Support contact email'),
  ('txn_success_rate_target', '95', 'analytics', 'Target success rate percentage');

INSERT INTO support_response_templates (template_name, subject, body, created_by) VALUES
  ('Recharge Failed', 'Regarding your recharge issue',
   'We apologize for the inconvenience. Your recharge is being investigated.', 2);

-- Dashboard alerts
INSERT INTO dashboard_alerts (alert_type, title, message, severity) VALUES
  ('service_down', 'Water Service API Offline', 'Water Service API is currently unreachable', 'critical'),
  ('failed_txn_spike', 'High failure rate detected', 'Transaction failures increased in last hour', 'warning'),
  ('kyc_pending', 'KYC reviews pending', '12 users awaiting KYC verification', 'info');

-- Analytics snapshots (last 7 days sample)
INSERT INTO analytics_daily_snapshots (
  snapshot_date, total_revenue, total_transactions, successful_txns, failed_txns,
  new_users, retained_users, avg_ticket_size, onboarded_users, paid_users
) VALUES
  (CURDATE() - INTERVAL 6 DAY, 12000.00, 180, 165, 15, 45, 120, 520.00, 200, 145),
  (CURDATE() - INTERVAL 5 DAY, 15000.00, 210, 195, 15, 52, 130, 580.00, 210, 160),
  (CURDATE() - INTERVAL 4 DAY, 11000.00, 165, 150, 15, 38, 115, 490.00, 180, 130),
  (CURDATE() - INTERVAL 3 DAY, 18000.00, 240, 220, 20, 60, 140, 610.00, 230, 175),
  (CURDATE() - INTERVAL 2 DAY, 16000.00, 220, 200, 20, 55, 135, 570.00, 215, 165),
  (CURDATE() - INTERVAL 1 DAY, 22000.00, 290, 270, 20, 70, 155, 640.00, 260, 200),
  (CURDATE(), 14000.00, 195, 175, 20, 48, 125, 550.00, 195, 150);

-- Referral code sample
INSERT INTO reffer_code (reffer_user_id, reffer_user_code, is_used) VALUES
  (1, 'BILLR123456789', 0);

-- =============================================================================
-- Default passwords for seed users: password123
-- Staff login: admin@billr.local / password123
-- Client login: 9876543210 / password123
-- =============================================================================
