-- Existing installation patch for online_orders payment gateway support + legal/settings keys.
-- Safe to import from phpMyAdmin on an already-installed database.
-- It only adds missing columns/settings and backfills payment defaults.

SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 0;

DROP PROCEDURE IF EXISTS apply_online_orders_payment_patch;
DELIMITER $$
CREATE PROCEDURE apply_online_orders_payment_patch()
BEGIN
    IF EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'online_orders'
    ) THEN
        -- online_orders.payment_method
        IF NOT EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'online_orders'
              AND COLUMN_NAME = 'payment_method'
        ) THEN
            ALTER TABLE online_orders
                ADD COLUMN payment_method VARCHAR(30) NOT NULL DEFAULT 'pay_later' AFTER total_amount;
        END IF;

        -- online_orders.payment_gateway
        IF NOT EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'online_orders'
              AND COLUMN_NAME = 'payment_gateway'
        ) THEN
            ALTER TABLE online_orders
                ADD COLUMN payment_gateway VARCHAR(30) DEFAULT NULL AFTER payment_method;
        END IF;

        -- online_orders.payment_status
        IF NOT EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'online_orders'
              AND COLUMN_NAME = 'payment_status'
        ) THEN
            ALTER TABLE online_orders
                ADD COLUMN payment_status VARCHAR(20) NOT NULL DEFAULT 'unpaid' AFTER payment_gateway;
        END IF;

        -- online_orders.gateway_session_id
        IF NOT EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'online_orders'
              AND COLUMN_NAME = 'gateway_session_id'
        ) THEN
            ALTER TABLE online_orders
                ADD COLUMN gateway_session_id VARCHAR(120) DEFAULT NULL AFTER payment_status;
        END IF;

        -- online_orders.gateway_transaction_id
        IF NOT EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'online_orders'
              AND COLUMN_NAME = 'gateway_transaction_id'
        ) THEN
            ALTER TABLE online_orders
                ADD COLUMN gateway_transaction_id VARCHAR(120) DEFAULT NULL AFTER gateway_session_id;
        END IF;

        -- online_orders.paid_at
        IF NOT EXISTS (
            SELECT 1
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = DATABASE()
              AND TABLE_NAME = 'online_orders'
              AND COLUMN_NAME = 'paid_at'
        ) THEN
            ALTER TABLE online_orders
                ADD COLUMN paid_at DATETIME DEFAULT NULL AFTER gateway_transaction_id;
        END IF;

        -- Backfill / normalize payment values for existing online orders.
        UPDATE online_orders
        SET payment_method = 'pay_later'
        WHERE payment_method IS NULL OR TRIM(payment_method) = '';

        UPDATE online_orders
        SET payment_status = CASE
            WHEN payment_method = 'pay_online' THEN 'pending'
            ELSE 'unpaid'
        END
        WHERE payment_status IS NULL OR TRIM(payment_status) = '';
    END IF;

    -- Settings keys used by the Thawani integration and public legal pages.
    INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('thawani_enabled', '0');
    INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('thawani_mode', 'sandbox');
    INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('thawani_publishable_key', '');
    INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('thawani_secret_key', '');
    INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('thawani_success_url', '');
    INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('thawani_cancel_url', '');
    INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('privacy_policy_content', '');
    INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('terms_conditions_content', '');
END $$
DELIMITER ;

CALL apply_online_orders_payment_patch();
DROP PROCEDURE IF EXISTS apply_online_orders_payment_patch;

SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;

-- Optional verification after import:
-- SHOW COLUMNS FROM online_orders;
-- SELECT setting_key, setting_value FROM settings WHERE setting_key IN (
--   'thawani_enabled', 'thawani_mode', 'thawani_publishable_key', 'thawani_secret_key',
--   'thawani_success_url', 'thawani_cancel_url', 'privacy_policy_content', 'terms_conditions_content'
-- );