-- Payment Alerts System Schema
-- Glado POS System

-- Table for tracking payment alerts sent
CREATE TABLE IF NOT EXISTS payment_alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_id INT NOT NULL,
    alert_type ENUM('overdue', 'upcoming', 'manual') NOT NULL,
    sent_via ENUM('whatsapp', 'email', 'both') DEFAULT 'whatsapp',
    sent_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    INDEX idx_purchase_alert (purchase_id, alert_type),
    INDEX idx_sent_date (sent_at)
);

-- Table for tracking payment completions
CREATE TABLE IF NOT EXISTS payment_completions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    purchase_id INT NOT NULL,
    payment_date DATE NOT NULL,
    payment_method VARCHAR(50),
    payment_reference VARCHAR(100),
    completed_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_purchase_completion (purchase_id),
    INDEX idx_payment_date (payment_date)
);

-- Add payment tracking fields to purchases table if they don't exist
ALTER TABLE purchases 
ADD COLUMN IF NOT EXISTS payment_date DATE NULL,
ADD COLUMN IF NOT EXISTS payment_method VARCHAR(50) NULL,
ADD COLUMN IF NOT EXISTS payment_reference VARCHAR(100) NULL;

-- Create indexes for better performance on payment queries
CREATE INDEX IF NOT EXISTS idx_purchases_status_franchise ON purchases(status, franchise_id);
CREATE INDEX IF NOT EXISTS idx_purchases_date_status ON purchases(purchase_date, status);

-- Add payment terms to suppliers table if it doesn't exist
ALTER TABLE suppliers 
ADD COLUMN IF NOT EXISTS payment_terms INT DEFAULT 30 COMMENT 'Payment terms in days';

-- Create view for payment dashboard
CREATE OR REPLACE VIEW payment_dashboard_view AS
SELECT 
    p.id,
    p.purchase_number,
    p.purchase_date,
    p.total_amount,
    p.status,
    p.payment_date,
    p.payment_method,
    s.name as supplier_name,
    s.email as supplier_email,
    s.phone as supplier_phone,
    s.payment_terms,
    DATE_ADD(p.purchase_date, INTERVAL s.payment_terms DAY) as due_date,
    DATEDIFF(CURDATE(), DATE_ADD(p.purchase_date, INTERVAL s.payment_terms DAY)) as days_overdue,
    DATEDIFF(DATE_ADD(p.purchase_date, INTERVAL s.payment_terms DAY), CURDATE()) as days_until_due,
    CASE 
        WHEN p.status = 'paid' THEN 'paid'
        WHEN DATEDIFF(CURDATE(), DATE_ADD(p.purchase_date, INTERVAL s.payment_terms DAY)) > 0 THEN 'overdue'
        WHEN DATEDIFF(DATE_ADD(p.purchase_date, INTERVAL s.payment_terms DAY), CURDATE()) <= 7 THEN 'due_soon'
        ELSE 'pending'
    END as payment_status,
    (SELECT COUNT(*) FROM payment_alerts pa WHERE pa.purchase_id = p.id) as alerts_sent,
    (SELECT MAX(pa.sent_at) FROM payment_alerts pa WHERE pa.purchase_id = p.id) as last_alert_sent
FROM purchases p
JOIN suppliers s ON p.supplier_id = s.id
WHERE p.status IN ('pending', 'received', 'paid');

-- Create stored procedure for automatic payment alert processing
DELIMITER //
CREATE OR REPLACE PROCEDURE ProcessAutomaticPaymentAlerts(IN franchise_id INT)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE purchase_id INT;
    DECLARE days_overdue INT;
    DECLARE days_until_due INT;
    
    -- Cursor for overdue payments
    DECLARE overdue_cursor CURSOR FOR
        SELECT p.id, DATEDIFF(CURDATE(), DATE_ADD(p.purchase_date, INTERVAL s.payment_terms DAY)) as days_overdue
        FROM purchases p
        JOIN suppliers s ON p.supplier_id = s.id
        WHERE p.franchise_id = franchise_id 
        AND p.status IN ('pending', 'received')
        AND DATEDIFF(CURDATE(), DATE_ADD(p.purchase_date, INTERVAL s.payment_terms DAY)) > 0
        AND NOT EXISTS (
            SELECT 1 FROM payment_alerts pa 
            WHERE pa.purchase_id = p.id 
            AND pa.alert_type = 'overdue' 
            AND DATE(pa.sent_at) = CURDATE()
        );
    
    -- Cursor for upcoming payments (3 days before due)
    DECLARE upcoming_cursor CURSOR FOR
        SELECT p.id, DATEDIFF(DATE_ADD(p.purchase_date, INTERVAL s.payment_terms DAY), CURDATE()) as days_until_due
        FROM purchases p
        JOIN suppliers s ON p.supplier_id = s.id
        WHERE p.franchise_id = franchise_id 
        AND p.status IN ('pending', 'received')
        AND DATEDIFF(DATE_ADD(p.purchase_date, INTERVAL s.payment_terms DAY), CURDATE()) = 3
        AND NOT EXISTS (
            SELECT 1 FROM payment_alerts pa 
            WHERE pa.purchase_id = p.id 
            AND pa.alert_type = 'upcoming' 
            AND DATE(pa.sent_at) = CURDATE()
        );
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- Process overdue payments
    OPEN overdue_cursor;
    overdue_loop: LOOP
        FETCH overdue_cursor INTO purchase_id, days_overdue;
        IF done THEN
            LEAVE overdue_loop;
        END IF;
        
        -- Mark for alert processing (will be handled by PHP service)
        INSERT INTO payment_alerts (purchase_id, alert_type, sent_at) 
        VALUES (purchase_id, 'overdue', NOW());
    END LOOP;
    CLOSE overdue_cursor;
    
    -- Reset done flag
    SET done = FALSE;
    
    -- Process upcoming payments
    OPEN upcoming_cursor;
    upcoming_loop: LOOP
        FETCH upcoming_cursor INTO purchase_id, days_until_due;
        IF done THEN
            LEAVE upcoming_loop;
        END IF;
        
        -- Mark for alert processing (will be handled by PHP service)
        INSERT INTO payment_alerts (purchase_id, alert_type, sent_at) 
        VALUES (purchase_id, 'upcoming', NOW());
    END LOOP;
    CLOSE upcoming_cursor;
    
END //
DELIMITER ;

-- Create event scheduler for automatic payment alerts (runs daily at 9 AM)
SET GLOBAL event_scheduler = ON;

CREATE EVENT IF NOT EXISTS daily_payment_alerts
ON SCHEDULE EVERY 1 DAY
STARTS TIMESTAMP(CURDATE() + INTERVAL 1 DAY, '09:00:00')
DO
BEGIN
    -- This will be called by the PHP cron job instead
    -- CALL ProcessAutomaticPaymentAlerts(1);
    
    -- Log the event execution
    INSERT INTO activity_logs (user_id, action, table_affected, old_values, new_values, created_at)
    VALUES (NULL, 'automatic_payment_alerts_scheduled', 'payment_alerts', NULL, 
            JSON_OBJECT('scheduled_at', NOW()), NOW());
END;

-- Sample data for testing (optional)
-- INSERT INTO payment_alerts (purchase_id, alert_type, sent_at) VALUES
-- (1, 'overdue', NOW() - INTERVAL 1 DAY),
-- (2, 'upcoming', NOW() - INTERVAL 2 HOURS);