-- ============================================================================= -- 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; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS analytics_daily_snapshots; DROP TABLE IF EXISTS dashboard_alerts; DROP TABLE IF EXISTS audit_logs; DROP TABLE IF EXISTS app_settings; DROP TABLE IF EXISTS support_response_templates; DROP TABLE IF EXISTS support_ticket_history; DROP TABLE IF EXISTS support_tickets; DROP TABLE IF EXISTS service_health_checks; DROP TABLE IF EXISTS external_services; DROP TABLE IF EXISTS agent_commission_ledger; DROP TABLE IF EXISTS financial_adjustments; DROP TABLE IF EXISTS service_commission_rules; DROP TABLE IF EXISTS transaction_actions; DROP TABLE IF EXISTS transactions; DROP TABLE IF EXISTS payment_gateways; DROP TABLE IF EXISTS service_categories; DROP TABLE IF EXISTS staff_activity_logs; DROP TABLE IF EXISTS staff_tokens; DROP TABLE IF EXISTS staff_roles; DROP TABLE IF EXISTS role_permissions; DROP TABLE IF EXISTS permissions; DROP TABLE IF EXISTS roles; DROP TABLE IF EXISTS staff_users; DROP TABLE IF EXISTS client_kyc_documents; DROP TABLE IF EXISTS client_login_history; DROP TABLE IF EXISTS client_tokens; DROP TABLE IF EXISTS reffer_code; DROP TABLE IF EXISTS password_reset_tokens; DROP TABLE IF EXISTS user_tokens; DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS client_management; SET FOREIGN_KEY_CHECKS = 1; -- ============================================================================= -- 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 -- =============================================================================