-- Database Schema untuk Payment System
-- Multi-Website Ebook Payment System with Midtrans
-- Websites: propertydiggest.com, ebookstore.com, matahariku.com

-- Tabel Orders untuk menyimpan semua transaksi
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(50) UNIQUE NOT NULL COMMENT 'Format: PD-EBOOK-timestamp, ES-EBOOK-timestamp, MH-EBOOK-timestamp',
    website VARCHAR(50) NOT NULL COMMENT 'propertydiggest.com, ebookstore.com, matahariku.com',
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    city VARCHAR(50) NOT NULL,
    amount DECIMAL(10,2) NOT NULL DEFAULT 99000.00,
    status VARCHAR(20) DEFAULT 'pending' COMMENT 'pending, success, failed, expired',
    transaction_id VARCHAR(100) NULL COMMENT 'Midtrans transaction ID',
    payment_type VARCHAR(50) NULL COMMENT 'credit_card, bank_transfer, etc',
    transaction_time DATETIME NULL,
    settlement_time DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_order_id (order_id),
    INDEX idx_website (website),
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Download Tokens untuk security (1x download)
CREATE TABLE IF NOT EXISTS download_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(50) NOT NULL,
    token VARCHAR(64) UNIQUE NOT NULL COMMENT 'SHA256 hash token',
    is_used TINYINT(1) DEFAULT 0 COMMENT '0 = belum digunakan, 1 = sudah digunakan',
    expires_at DATETIME NOT NULL COMMENT 'Token expire 24 jam setelah dibuat',
    used_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_order_id (order_id),
    INDEX idx_expires (expires_at),
    INDEX idx_used (is_used)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Email Logs untuk tracking
CREATE TABLE IF NOT EXISTS email_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(50) NOT NULL,
    email_to VARCHAR(100) NOT NULL,
    email_type VARCHAR(30) NOT NULL COMMENT 'admin_notification, customer_download',
    subject VARCHAR(200) NOT NULL,
    status VARCHAR(20) DEFAULT 'sent' COMMENT 'sent, failed',
    error_message TEXT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    INDEX idx_order_id (order_id),
    INDEX idx_email_type (email_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabel Download Logs untuk audit
CREATE TABLE IF NOT EXISTS download_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(50) NOT NULL,
    token VARCHAR(64) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT NULL,
    downloaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    INDEX idx_order_id (order_id),
    INDEX idx_token (token),
    INDEX idx_downloaded (downloaded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data untuk testing (optional, bisa dihapus di production)
-- INSERT INTO orders (order_id, website, name, email, phone, city, amount, status) VALUES
-- ('PD-EBOOK-1234567890', 'propertydiggest.com', 'Test User', 'test@example.com', '081234567890', 'Jakarta', 99000.00, 'success');