-- Customer & Credit Management System Database Schema
-- All tables are prefixed with 'customer_' and use business code prefixing
-- This file contains the base schema that will be adapted for each business

-- ====================================
-- 1. Customer Accounts (Main Table)
-- ====================================
CREATE TABLE IF NOT EXISTS `{business_code}_customer_accounts` (
    `customer_id` int(11) NOT NULL AUTO_INCREMENT,
    `customer_code` varchar(20) NOT NULL COMMENT 'Unique customer identifier within business',
    `customer_name` varchar(100) NOT NULL,
    `customer_phone` varchar(20) DEFAULT NULL,
    `customer_email` varchar(100) DEFAULT NULL,
    `customer_address` text DEFAULT NULL,
    `customer_type` enum('individual', 'business') DEFAULT 'individual',
    `tax_id` varchar(50) DEFAULT NULL COMMENT 'TIN or business registration number',
    `credit_enabled` tinyint(1) DEFAULT 0 COMMENT 'Whether customer can purchase on credit',
    `customer_status` enum('active', 'inactive', 'suspended') DEFAULT 'active',
    `date_of_birth` date DEFAULT NULL,
    `notes` text DEFAULT NULL,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`customer_id`),
    UNIQUE KEY `uk_customer_code` (`customer_code`),
    UNIQUE KEY `uk_customer_phone` (`customer_phone`),
    KEY `idx_customer_name` (`customer_name`),
    KEY `idx_customer_status` (`customer_status`),
    KEY `idx_credit_enabled` (`credit_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 2. Customer Credit Accounts
-- ====================================
CREATE TABLE IF NOT EXISTS `{business_code}_customer_credit_accounts` (
    `credit_account_id` int(11) NOT NULL AUTO_INCREMENT,
    `customer_id` int(11) NOT NULL,
    `credit_limit` decimal(15,2) DEFAULT 0.00 COMMENT 'Maximum credit limit',
    `current_balance` decimal(15,2) DEFAULT 0.00 COMMENT 'Current outstanding balance',
    `available_credit` decimal(15,2) DEFAULT 0.00 COMMENT 'Available credit (limit - balance)',
    `payment_terms` enum('cash', 'net_7', 'net_15', 'net_30', 'net_45', 'net_60') DEFAULT 'net_30',
    `interest_rate` decimal(5,2) DEFAULT 0.00 COMMENT 'Monthly interest rate percentage',
    `last_payment_date` date DEFAULT NULL,
    `last_purchase_date` date DEFAULT NULL,
    `overdue_amount` decimal(15,2) DEFAULT 0.00,
    `days_overdue` int(11) DEFAULT 0,
    `credit_status` enum('good', 'warning', 'overdue', 'suspended') DEFAULT 'good',
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`credit_account_id`),
    UNIQUE KEY `uk_customer_credit` (`customer_id`),
    FOREIGN KEY (`customer_id`) REFERENCES `{business_code}_customer_accounts` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 3. Customer Transactions (Credit Ledger)
-- ====================================
CREATE TABLE IF NOT EXISTS `{business_code}_customer_transactions` (
    `customer_transaction_id` int(11) NOT NULL AUTO_INCREMENT,
    `customer_id` int(11) NOT NULL,
    `sales_transaction_id` int(11) DEFAULT NULL COMMENT 'Link to sales transaction if applicable',
    `transaction_type` enum('sale', 'payment', 'credit_adjustment', 'interest_charge', 'fee') NOT NULL,
    `amount` decimal(15,2) NOT NULL COMMENT 'Transaction amount (positive for charges, negative for payments)',
    `balance_before` decimal(15,2) NOT NULL COMMENT 'Balance before this transaction',
    `balance_after` decimal(15,2) NOT NULL COMMENT 'Balance after this transaction',
    `payment_method_id` INT DEFAULT NULL,
    `reference_number` varchar(50) DEFAULT NULL COMMENT 'Check number, transfer reference, etc.',
    `transaction_date` timestamp DEFAULT CURRENT_TIMESTAMP,
    `due_date` date DEFAULT NULL COMMENT 'Due date for credit sales',
    `notes` text DEFAULT NULL,
    `processed_by` varchar(50) DEFAULT NULL COMMENT 'Staff member who processed',
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`customer_transaction_id`),
    KEY `idx_customer_id` (`customer_id`),
    KEY `idx_transaction_type` (`transaction_type`),
    KEY `idx_transaction_date` (`transaction_date`),
    KEY `idx_sales_transaction` (`sales_transaction_id`),
    INDEX `idx_payment_method_id` (`payment_method_id`),
    FOREIGN KEY (`payment_method_id`) REFERENCES `{business_code}_payment_method`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`customer_id`) REFERENCES `{business_code}_customer_accounts` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 4. Customer Payment History
-- ====================================
CREATE TABLE IF NOT EXISTS `{business_code}_customer_payment_history` (
    `payment_id` int(11) NOT NULL AUTO_INCREMENT,
    `customer_id` int(11) NOT NULL,
    `customer_transaction_id` int(11) NOT NULL,
    `payment_amount` decimal(15,2) NOT NULL,
    `payment_method_id` INT NOT NULL,
    `reference_number` varchar(50) DEFAULT NULL,
    `payment_date` timestamp DEFAULT CURRENT_TIMESTAMP,
    `processed_by` varchar(50) DEFAULT NULL,
    `bank_name` varchar(100) DEFAULT NULL,
    `check_date` date DEFAULT NULL,
    `notes` text DEFAULT NULL,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`payment_id`),
    KEY `idx_customer_id` (`customer_id`),
    KEY `idx_payment_date` (`payment_date`),
    KEY `idx_payment_method_id` (`payment_method_id`),
    FOREIGN KEY (`payment_method_id`) REFERENCES `{business_code}_payment_method`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`customer_id`) REFERENCES `{business_code}_customer_accounts` (`customer_id`) ON DELETE CASCADE,
    FOREIGN KEY (`customer_transaction_id`) REFERENCES `{business_code}_customer_transactions` (`customer_transaction_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 5. Customer Contact History
-- ====================================
CREATE TABLE IF NOT EXISTS `{business_code}_customer_contact_history` (
    `contact_id` int(11) NOT NULL AUTO_INCREMENT,
    `customer_id` int(11) NOT NULL,
    `contact_type` enum('call', 'email', 'sms', 'visit', 'other') NOT NULL,
    `contact_reason` enum('payment_reminder', 'overdue_notice', 'general_inquiry', 'complaint', 'other') NOT NULL,
    `contact_details` text DEFAULT NULL,
    `contact_date` timestamp DEFAULT CURRENT_TIMESTAMP,
    `contacted_by` varchar(50) DEFAULT NULL,
    `follow_up_required` tinyint(1) DEFAULT 0,
    `follow_up_date` date DEFAULT NULL,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`contact_id`),
    KEY `idx_customer_id` (`customer_id`),
    KEY `idx_contact_date` (`contact_date`),
    KEY `idx_contact_type` (`contact_type`),
    FOREIGN KEY (`customer_id`) REFERENCES `{business_code}_customer_accounts` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 6. Customer Purchase Summary (for quick reporting)
-- ====================================
CREATE TABLE IF NOT EXISTS `{business_code}_customer_purchase_summary` (
    `summary_id` int(11) NOT NULL AUTO_INCREMENT,
    `customer_id` int(11) NOT NULL,
    `summary_period` enum('monthly', 'yearly') NOT NULL,
    `period_year` int(4) NOT NULL,
    `period_month` int(2) DEFAULT NULL COMMENT 'NULL for yearly summaries',
    `total_purchases` decimal(15,2) DEFAULT 0.00,
    `total_payments` decimal(15,2) DEFAULT 0.00,
    `purchase_count` int(11) DEFAULT 0,
    `payment_count` int(11) DEFAULT 0,
    `average_purchase` decimal(15,2) DEFAULT 0.00,
    `largest_purchase` decimal(15,2) DEFAULT 0.00,
    `last_updated` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`summary_id`),
    UNIQUE KEY `uk_customer_period` (`customer_id`, `summary_period`, `period_year`, `period_month`),
    KEY `idx_period` (`summary_period`, `period_year`, `period_month`),
    FOREIGN KEY (`customer_id`) REFERENCES `{business_code}_customer_accounts` (`customer_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================
-- 7. Customer Sequences for Code Generation
-- ====================================
CREATE TABLE IF NOT EXISTS `{business_code}_customer_sequences` (
    `sequence_name` varchar(50) NOT NULL,
    `current_value` int(11) NOT NULL DEFAULT 0,
    `updated_at` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`sequence_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initialize customer code sequence
INSERT INTO `{business_code}_customer_sequences` (`sequence_name`, `current_value`) 
VALUES ('customer_code', 0) 
ON DUPLICATE KEY UPDATE sequence_name = sequence_name;

-- ====================================
-- 8. Sample Data and Stored Procedures
-- ====================================

-- Note: Stored procedures and triggers are handled in PHP code for better compatibility
-- The customer code generation is handled in the PHP API
-- Credit account updates are handled in the PHP API

-- Sample customer data (optional)
-- INSERT INTO `{business_code}_customer_accounts` 
-- (customer_name, customer_phone, customer_email, customer_type, credit_enabled) 
-- VALUES 
-- ('Walk-in Customer', NULL, NULL, 'individual', 0),
-- ('Cash Customer', NULL, NULL, 'individual', 0);

-- ====================================
-- Indexes for Performance
-- ====================================
-- Additional indexes for better query performance
CREATE INDEX idx_customer_phone_lookup ON `{business_code}_customer_accounts` (customer_phone, customer_status);
CREATE INDEX idx_customer_credit_status ON `{business_code}_customer_credit_accounts` (credit_status, overdue_amount);
CREATE INDEX idx_customer_transactions_date_type ON `{business_code}_customer_transactions` (transaction_date, transaction_type, customer_id);
CREATE INDEX idx_payment_history_date ON `{business_code}_customer_payment_history` (payment_date, customer_id);

-- ====================================
-- Views for Common Queries
-- ====================================

-- Customer Summary View
CREATE VIEW `{business_code}_customer_summary_view` AS
SELECT 
    ca.customer_id,
    ca.customer_code,
    ca.customer_name,
    ca.customer_phone,
    ca.customer_email,
    ca.customer_status,
    ca.credit_enabled,
    COALESCE(cca.credit_limit, 0) as credit_limit,
    COALESCE(cca.current_balance, 0) as current_balance,
    COALESCE(cca.available_credit, 0) as available_credit,
    COALESCE(cca.overdue_amount, 0) as overdue_amount,
    COALESCE(cca.days_overdue, 0) as days_overdue,
    COALESCE(cca.credit_status, 'good') as credit_status,
    cca.last_purchase_date,
    cca.last_payment_date,
    (SELECT COUNT(*) FROM `{business_code}_customer_transactions` ct 
     WHERE ct.customer_id = ca.customer_id AND ct.transaction_type = 'sale') as total_purchases,
    (SELECT COALESCE(SUM(ABS(amount)), 0) FROM `{business_code}_customer_transactions` ct 
     WHERE ct.customer_id = ca.customer_id AND ct.transaction_type = 'sale') as total_purchase_amount,
    ca.created_at as customer_since
FROM `{business_code}_customer_accounts` ca
LEFT JOIN `{business_code}_customer_credit_accounts` cca ON ca.customer_id = cca.customer_id;

-- Outstanding Balance View
CREATE VIEW `{business_code}_customer_outstanding_view` AS
SELECT 
    ca.customer_id,
    ca.customer_code,
    ca.customer_name,
    ca.customer_phone,
    cca.current_balance,
    cca.overdue_amount,
    cca.days_overdue,
    cca.credit_status,
    cca.last_payment_date,
    (SELECT MAX(due_date) FROM `{business_code}_customer_transactions` ct 
     WHERE ct.customer_id = ca.customer_id 
     AND ct.transaction_type = 'sale' 
     AND ct.due_date IS NOT NULL 
     AND ct.amount > 0) as next_due_date
FROM `{business_code}_customer_accounts` ca
INNER JOIN `{business_code}_customer_credit_accounts` cca ON ca.customer_id = cca.customer_id
WHERE cca.current_balance > 0
ORDER BY cca.days_overdue DESC, cca.current_balance DESC; 