-- Migration: Create payment system tables
-- สำหรับระบบชำระเงินและการสั่งซื้อคอร์ส

-- Drop existing tables
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS shopping_cart;
SET FOREIGN_KEY_CHECKS=1;

-- Table: shopping_cart (ตะกร้าสินค้า)
CREATE TABLE IF NOT EXISTS shopping_cart (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT(20) UNSIGNED NOT NULL,
    course_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_id),
    INDEX idx_course (course_id),
    
    CONSTRAINT fk_cart_user 
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_cart_course 
        FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
    
    UNIQUE KEY unique_user_course (user_id, course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: orders (คำสั่งซื้อ)
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    user_id BIGINT(20) UNSIGNED NOT NULL,
    
    -- Order totals
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) DEFAULT 0.00,
    tax_amount DECIMAL(10,2) DEFAULT 0.00,
    total_amount DECIMAL(10,2) NOT NULL,
    
    -- Customer info
    billing_name VARCHAR(255) NOT NULL,
    billing_email VARCHAR(255) NOT NULL,
    billing_phone VARCHAR(20),
    billing_address TEXT,
    
    -- Payment info
    payment_method ENUM('credit_card', 'bank_transfer', 'promptpay', 'truemoney') DEFAULT 'credit_card',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded', 'cancelled') DEFAULT 'pending',
    payment_reference VARCHAR(100),
    payment_date TIMESTAMP NULL,
    
    -- Order status
    status ENUM('pending', 'processing', 'completed', 'cancelled', 'refunded') DEFAULT 'pending',
    notes TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_id),
    INDEX idx_order_number (order_number),
    INDEX idx_status (status),
    INDEX idx_payment_status (payment_status),
    INDEX idx_created_at (created_at),
    
    CONSTRAINT fk_orders_user 
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table: order_items (รายการสินค้าในคำสั่งซื้อ)
CREATE TABLE IF NOT EXISTS order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    course_id INT NOT NULL,
    
    -- Item details at time of purchase
    course_title VARCHAR(255) NOT NULL,
    course_price DECIMAL(10,2) NOT NULL,
    discount_price DECIMAL(10,2) DEFAULT NULL,
    final_price DECIMAL(10,2) NOT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_order (order_id),
    INDEX idx_course (course_id),
    
    CONSTRAINT fk_order_items_order 
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_order_items_course 
        FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add some indexes for better performance
ALTER TABLE orders ADD INDEX idx_payment_date (payment_date);
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
ALTER TABLE order_items ADD INDEX idx_order_course (order_id, course_id);

-- Insert sample payment methods data (for future use)
INSERT IGNORE INTO site_settings (setting_key, setting_value, setting_description) VALUES
('payment_enabled', '1', 'Enable payment system'),
('payment_currency', 'THB', 'Payment currency'),
('payment_tax_rate', '7', 'VAT tax rate percentage'),
('payment_bank_account', '', 'Bank account for bank transfer'),
('payment_promptpay_id', '', 'PromptPay ID/Phone number'),
('payment_stripe_key', '', 'Stripe publishable key'),
('payment_stripe_secret', '', 'Stripe secret key');

SELECT 'Payment system database schema created successfully!' as result;