-- =============================================================================
-- India Market Extension — run on existing driftdevelopers_biller_datatable DB
--   mysql -u root -p driftdevelopers_biller_datatable < schema_india_extension.sql
-- =============================================================================

USE driftdevelopers_biller_datatable;

-- Phone Bill category (missing from original 6 services)
INSERT INTO service_categories (category_code, category_name, sort_order) VALUES
  ('phone_bill', 'Phone Bill', 7)
ON DUPLICATE KEY UPDATE category_name = VALUES(category_name);

INSERT INTO external_services (service_code, service_name) VALUES
  ('phone_bill_api', 'Phone Bill API')
ON DUPLICATE KEY UPDATE service_name = VALUES(service_name);

-- Bill payment operators (Indian market)
CREATE TABLE IF NOT EXISTS service_operators (
  operator_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_id       INT UNSIGNED NOT NULL,
  operator_code     VARCHAR(30)  NOT NULL,
  operator_name     VARCHAR(100) NOT NULL,
  circle            VARCHAR(50)  DEFAULT NULL COMMENT 'Delhi, Mumbai, Karnataka, etc.',
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (operator_id),
  UNIQUE KEY uq_operator_code (operator_code),
  KEY idx_op_category (category_id),
  CONSTRAINT fk_op_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;

-- Payment methods popular in India
CREATE TABLE IF NOT EXISTS payment_methods (
  method_id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  method_code       VARCHAR(30)  NOT NULL,
  method_name       VARCHAR(50)  NOT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (method_id),
  UNIQUE KEY uq_method_code (method_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Customer wallet (Paytm/PhonePe style)
CREATE TABLE IF NOT EXISTS client_wallets (
  wallet_id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  client_user_id    INT UNSIGNED NOT NULL,
  balance           DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  currency          CHAR(3)      NOT NULL DEFAULT 'INR',
  status            ENUM('active','frozen','closed') NOT NULL DEFAULT 'active',
  updated_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (wallet_id),
  UNIQUE KEY uq_wallet_client (client_user_id),
  CONSTRAINT fk_wallet_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;

CREATE TABLE IF NOT EXISTS wallet_transactions (
  wt_id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  wallet_id         INT UNSIGNED NOT NULL,
  txn_type          ENUM('credit','debit','refund','cashback') NOT NULL,
  amount            DECIMAL(12,2) NOT NULL,
  balance_after     DECIMAL(12,2) NOT NULL,
  reference_type    VARCHAR(30)  DEFAULT NULL COMMENT 'bill_payment, topup, refund',
  reference_id      VARCHAR(50)  DEFAULT NULL,
  description       VARCHAR(255) DEFAULT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (wt_id),
  KEY idx_wt_wallet (wallet_id),
  CONSTRAINT fk_wt_wallet
    FOREIGN KEY (wallet_id) REFERENCES client_wallets (wallet_id)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Invoices / receipts (GST-compliant Indian billing)
CREATE TABLE IF NOT EXISTS invoices (
  invoice_id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
  invoice_number    VARCHAR(30)  NOT NULL COMMENT 'e.g. INV-2025-00001',
  transaction_id    INT UNSIGNED DEFAULT NULL,
  client_user_id    INT UNSIGNED NOT NULL,
  category_id       INT UNSIGNED NOT NULL,
  subtotal          DECIMAL(12,2) NOT NULL,
  gst_amount        DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  convenience_fee   DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_amount      DECIMAL(12,2) NOT NULL,
  currency          CHAR(3)      NOT NULL DEFAULT 'INR',
  payment_method    VARCHAR(30)  DEFAULT NULL,
  status            ENUM('draft','issued','paid','cancelled','refunded') NOT NULL DEFAULT 'issued',
  beneficiary_name  VARCHAR(100) DEFAULT NULL,
  beneficiary_ref   VARCHAR(100) DEFAULT NULL COMMENT 'Consumer no / mobile / CA number',
  operator_name     VARCHAR(50)  DEFAULT NULL,
  invoice_date      DATE         NOT NULL,
  created_at        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (invoice_id),
  UNIQUE KEY uq_invoice_number (invoice_number),
  KEY idx_inv_client (client_user_id),
  KEY idx_inv_txn (transaction_id),
  CONSTRAINT fk_inv_client
    FOREIGN KEY (client_user_id) REFERENCES client_management (client_user_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_inv_txn
    FOREIGN KEY (transaction_id) REFERENCES transactions (transaction_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_inv_category
    FOREIGN KEY (category_id) REFERENCES service_categories (category_id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Support complaint categories (Indian BBPS / telecom norms)
CREATE TABLE IF NOT EXISTS complaint_categories (
  category_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
  category_code     VARCHAR(30)  NOT NULL,
  category_name     VARCHAR(100) NOT NULL,
  service_type      VARCHAR(50)  DEFAULT NULL,
  is_active         TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (category_id),
  UNIQUE KEY uq_complaint_code (category_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Customer support call logs
CREATE TABLE IF NOT EXISTS support_call_logs (
  call_id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  call_ref          VARCHAR(20)  NOT NULL,
  client_user_id    INT UNSIGNED NOT NULL,
  ticket_id         INT UNSIGNED DEFAULT NULL,
  staff_id          INT UNSIGNED DEFAULT NULL,
  complaint_category_id INT UNSIGNED DEFAULT NULL,
  caller_phone      VARCHAR(20)  NOT NULL,
  call_direction    ENUM('inbound','outbound') NOT NULL DEFAULT 'inbound',
  call_duration_sec INT UNSIGNED DEFAULT 0,
  call_status       ENUM('answered','missed','voicemail','callback_scheduled','resolved') NOT NULL DEFAULT 'answered',
  summary           TEXT         DEFAULT NULL,
  resolution        TEXT         DEFAULT NULL,
  callback_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 (call_id),
  UNIQUE KEY uq_call_ref (call_ref),
  KEY idx_call_client (client_user_id),
  KEY idx_call_staff (staff_id),
  CONSTRAINT fk_call_client
    FOREIGN KEY (client_user_id) REFERENCES client_management (client_user_id)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT fk_call_ticket
    FOREIGN KEY (ticket_id) REFERENCES support_tickets (ticket_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_call_staff
    FOREIGN KEY (staff_id) REFERENCES staff_users (staff_id)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT fk_call_complaint
    FOREIGN KEY (complaint_category_id) REFERENCES complaint_categories (category_id)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Extend support tickets with channel & complaint type
ALTER TABLE support_tickets
  ADD COLUMN IF NOT EXISTS channel ENUM('app','call','email','whatsapp') NOT NULL DEFAULT 'app' AFTER priority,
  ADD COLUMN IF NOT EXISTS complaint_category_id INT UNSIGNED DEFAULT NULL AFTER channel;

-- Seed operators (Indian market)
INSERT INTO service_operators (category_id, operator_code, operator_name, circle) VALUES
  ((SELECT category_id FROM service_categories WHERE category_code='recharge'), 'airtel', 'Airtel', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='recharge'), 'jio', 'Jio', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='recharge'), 'vi', 'Vi (Vodafone Idea)', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='recharge'), 'bsnl', 'BSNL', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='phone_bill'), 'airtel_postpaid', 'Airtel Postpaid', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='phone_bill'), 'jio_postpaid', 'Jio Postpaid', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='electricity'), 'bses', 'BSES Rajdhani', 'Delhi'),
  ((SELECT category_id FROM service_categories WHERE category_code='electricity'), 'tata_power', 'Tata Power', 'Mumbai'),
  ((SELECT category_id FROM service_categories WHERE category_code='electricity'), 'mseb', 'MSEB', 'Maharashtra'),
  ((SELECT category_id FROM service_categories WHERE category_code='dth'), 'tata_sky', 'Tata Play', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='dth'), 'dish_tv', 'Dish TV', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='dth'), 'airtel_dth', 'Airtel DTH', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='internet'), 'airtel_fiber', 'Airtel Xstream Fiber', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='internet'), 'jio_fiber', 'JioFiber', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='water'), 'delhi_jal', 'Delhi Jal Board', 'Delhi'),
  ((SELECT category_id FROM service_categories WHERE category_code='credit_card'), 'hdfc_cc', 'HDFC Credit Card', 'Pan India'),
  ((SELECT category_id FROM service_categories WHERE category_code='credit_card'), 'sbi_cc', 'SBI Credit Card', 'Pan India')
ON DUPLICATE KEY UPDATE operator_name = VALUES(operator_name);

INSERT INTO payment_methods (method_code, method_name) VALUES
  ('upi', 'UPI'),
  ('paytm', 'Paytm Wallet'),
  ('phonepe', 'PhonePe'),
  ('gpay', 'Google Pay'),
  ('netbanking', 'Net Banking'),
  ('debit_card', 'Debit Card'),
  ('credit_card', 'Credit Card'),
  ('billr_wallet', 'Billr Wallet')
ON DUPLICATE KEY UPDATE method_name = VALUES(method_name);

INSERT INTO complaint_categories (category_code, category_name, service_type) VALUES
  ('recharge_failed', 'Recharge Failed / Pending', 'recharge'),
  ('wrong_number', 'Wrong Number Recharged', 'recharge'),
  ('bill_not_updated', 'Bill Payment Not Reflected', 'electricity'),
  ('double_debit', 'Double Debit / Duplicate Charge', 'general'),
  ('refund_pending', 'Refund Not Received', 'general'),
  ('kyc_issue', 'KYC Verification Issue', 'account'),
  ('login_issue', 'Login / OTP Issue', 'account'),
  ('dth_activation', 'DTH Not Activated', 'dth'),
  ('water_dispute', 'Water Bill Dispute', 'water'),
  ('phone_bill_dispute', 'Postpaid Bill Dispute', 'phone_bill')
ON DUPLICATE KEY UPDATE category_name = VALUES(category_name);

-- Wallets for existing users
INSERT INTO client_wallets (client_user_id, balance) VALUES
  (1, 1250.00), (2, 0.00), (3, 580.50)
ON DUPLICATE KEY UPDATE balance = VALUES(balance);

INSERT INTO wallet_transactions (wallet_id, txn_type, amount, balance_after, reference_type, description)
SELECT w.wallet_id, 'credit', 1250.00, 1250.00, 'topup', 'Initial wallet top-up via UPI'
FROM client_wallets w JOIN client_management c ON c.client_user_id = w.client_user_id
WHERE c.client_phone = '1234567890'
ON DUPLICATE KEY UPDATE description = description;

-- Sample invoices
INSERT INTO invoices (
  invoice_number, transaction_id, client_user_id, category_id,
  subtotal, gst_amount, convenience_fee, total_amount, payment_method,
  status, beneficiary_ref, operator_name, invoice_date
) VALUES
  ('INV-2025-00001', 1, 1,
   (SELECT category_id FROM service_categories WHERE category_code='recharge'),
   199.00, 0.00, 2.00, 201.00, 'upi', 'paid', '9876543210', 'Airtel', '2025-06-02'),
  ('INV-2025-00002', 3, 3,
   (SELECT category_id FROM service_categories WHERE category_code='electricity'),
   250.00, 0.00, 10.00, 260.00, 'paytm', 'paid', 'CON67890', 'Tata Power', CURDATE())
ON DUPLICATE KEY UPDATE total_amount = VALUES(total_amount);

-- Sample support calls
INSERT INTO support_call_logs (
  call_ref, client_user_id, staff_id, complaint_category_id,
  caller_phone, call_direction, call_duration_sec, call_status, summary
) VALUES
  ('CALL-1001', 1, 2,
   (SELECT category_id FROM complaint_categories WHERE category_code='recharge_failed'),
   '1234567890', 'inbound', 420, 'resolved', 'Customer reported failed Airtel recharge. Refund initiated.'),
  ('CALL-1002', 2, 2,
   (SELECT category_id FROM complaint_categories WHERE category_code='login_issue'),
   '9876543210', 'inbound', 180, 'callback_scheduled', 'OTP not received. Callback scheduled for 4 PM IST.')
ON DUPLICATE KEY UPDATE summary = VALUES(summary);

-- Commission rule for phone bill
INSERT INTO service_commission_rules (category_id, service_charge, convenience_fee, flat_commission, effective_from)
SELECT category_id, 12.00, 2.00, 6.00, '2025-01-01'
FROM service_categories WHERE category_code = 'phone_bill'
ON DUPLICATE KEY UPDATE flat_commission = VALUES(flat_commission);

-- New permissions
INSERT INTO permissions (permission_key, permission_label, module_name) VALUES
  ('bill_payments', 'Bill Payments', 'Bill Payments'),
  ('invoices', 'Invoices', 'Invoices'),
  ('support_calls', 'Support Calls', 'Customer Support'),
  ('wallet', 'Wallet', 'Wallet Management')
ON DUPLICATE KEY UPDATE permission_label = VALUES(permission_label);

INSERT INTO role_permissions (role_id, permission_id)
SELECT 1, permission_id FROM permissions
WHERE permission_key IN ('bill_payments','invoices','support_calls','wallet')
ON DUPLICATE KEY UPDATE role_id = role_id;

-- App settings for India
INSERT INTO app_settings (setting_key, setting_value, setting_group, description) VALUES
  ('gst_rate', '18', 'billing', 'GST rate percentage for convenience fees'),
  ('support_helpline', '1800-123-4567', 'support', 'Toll-free customer support number'),
  ('support_hours', '9:00 AM - 9:00 PM IST', 'support', 'Support operating hours'),
  ('bbps_enabled', '1', 'billing', 'BBPS bill payment enabled'),
  ('min_recharge_amount', '10', 'billing', 'Minimum mobile recharge in INR'),
  ('max_recharge_amount', '10000', 'billing', 'Maximum single recharge in INR')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);