-- History Database Schema for GoStream Application
-- This file contains the database schema for history-related tables

-- Transaction History Table
CREATE TABLE IF NOT EXISTS transaction_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    type ENUM('deposit', 'withdrawal', 'purchase', 'commission', 'refund') NOT NULL,
    method VARCHAR(100) NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    description TEXT,
    reference_id VARCHAR(100) UNIQUE,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_type (type),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_reference_id (reference_id),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Affiliate Commission History Table
CREATE TABLE IF NOT EXISTS affiliate_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    affiliate_link_id INT,
    link_name VARCHAR(255) NOT NULL,
    link_url TEXT NOT NULL,
    platform ENUM('shopee', 'tiktok', 'lazada', 'instagram', 'youtube', 'other') NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    commission_rate DECIMAL(5, 2) NOT NULL,
    status ENUM('pending', 'approved', 'paid', 'rejected') DEFAULT 'pending',
    clicks INT DEFAULT 0,
    conversions INT DEFAULT 0,
    conversion_value DECIMAL(15, 2) DEFAULT 0,
    payment_date TIMESTAMP NULL,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_platform (platform),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_payment_date (payment_date),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- SMM Order History Table
CREATE TABLE IF NOT EXISTS smm_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    service_id VARCHAR(100) NOT NULL,
    service_name VARCHAR(255) NOT NULL,
    platform ENUM('shopee', 'tiktok', 'lazada', 'instagram', 'youtube', 'facebook', 'twitter', 'other') NOT NULL,
    description TEXT,
    target_url TEXT NOT NULL,
    quantity INT NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'cancelled', 'partial') DEFAULT 'pending',
    start_count INT DEFAULT 0,
    current_count INT DEFAULT 0,
    remains INT DEFAULT 0,
    progress_percentage DECIMAL(5, 2) DEFAULT 0,
    start_date TIMESTAMP NULL,
    completion_date TIMESTAMP NULL,
    api_order_id VARCHAR(255),
    api_response JSON,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_platform (platform),
    INDEX idx_status (status),
    INDEX idx_service_id (service_id),
    INDEX idx_created_at (created_at),
    INDEX idx_api_order_id (api_order_id),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Wallet Transaction History Table (for Go-Coins)
CREATE TABLE IF NOT EXISTS wallet_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    transaction_type ENUM('credit', 'debit') NOT NULL,
    amount DECIMAL(15, 2) NOT NULL,
    balance_before DECIMAL(15, 2) NOT NULL,
    balance_after DECIMAL(15, 2) NOT NULL,
    source ENUM('deposit', 'withdrawal', 'purchase', 'commission', 'bonus', 'refund', 'transfer') NOT NULL,
    source_id INT,
    description TEXT,
    reference_id VARCHAR(100),
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_transaction_type (transaction_type),
    INDEX idx_source (source),
    INDEX idx_created_at (created_at),
    INDEX idx_reference_id (reference_id),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Activity Log Table (for tracking user activities)
CREATE TABLE IF NOT EXISTS activity_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    activity_type VARCHAR(100) NOT NULL,
    activity_description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_activity_type (activity_type),
    INDEX idx_created_at (created_at),
    INDEX idx_ip_address (ip_address),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Notification History Table
CREATE TABLE IF NOT EXISTS notification_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'success', 'warning', 'error') DEFAULT 'info',
    is_read BOOLEAN DEFAULT FALSE,
    action_url VARCHAR(500),
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    read_at TIMESTAMP NULL,
    
    INDEX idx_user_id (user_id),
    INDEX idx_type (type),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Create views for easier querying
CREATE OR REPLACE VIEW v_user_transaction_summary AS
SELECT 
    user_id,
    COUNT(*) as total_transactions,
    SUM(CASE WHEN type = 'deposit' AND status = 'completed' THEN amount ELSE 0 END) as total_deposits,
    SUM(CASE WHEN type = 'withdrawal' AND status = 'completed' THEN amount ELSE 0 END) as total_withdrawals,
    SUM(CASE WHEN type = 'commission' AND status = 'completed' THEN amount ELSE 0 END) as total_commissions,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_transactions,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_transactions,
    COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_transactions
FROM transaction_history 
GROUP BY user_id;

CREATE OR REPLACE VIEW v_user_affiliate_summary AS
SELECT 
    user_id,
    COUNT(*) as total_commissions,
    COUNT(DISTINCT link_name) as total_links,
    SUM(clicks) as total_clicks,
    SUM(conversions) as total_conversions,
    SUM(CASE WHEN status IN ('approved', 'paid') THEN amount ELSE 0 END) as total_earnings,
    CASE 
        WHEN SUM(clicks) > 0 THEN ROUND((SUM(conversions) / SUM(clicks)) * 100, 2)
        ELSE 0 
    END as conversion_rate,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_commissions,
    COUNT(CASE WHEN status = 'approved' THEN 1 END) as approved_commissions,
    COUNT(CASE WHEN status = 'paid' THEN 1 END) as paid_commissions
FROM affiliate_history 
GROUP BY user_id;

CREATE OR REPLACE VIEW v_user_smm_summary AS
SELECT 
    user_id,
    COUNT(*) as total_orders,
    SUM(amount) as total_spent,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
    COUNT(CASE WHEN status = 'processing' THEN 1 END) as processing_orders,
    COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_orders,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_orders,
    AVG(progress_percentage) as avg_progress,
    COUNT(DISTINCT platform) as platforms_used
FROM smm_history 
GROUP BY user_id;

-- Insert sample data for testing
INSERT INTO transaction_history (user_id, type, method, amount, status, description, reference_id) VALUES
(1, 'deposit', 'Transfer Bank', 500000, 'completed', 'Deposit via Transfer Bank BCA', 'TXN001'),
(1, 'withdrawal', 'E-Wallet', 150000, 'completed', 'Penarikan ke OVO', 'TXN002'),
(1, 'purchase', 'Go-Coins', 75000, 'completed', 'Pembelian layanan SMM', 'TXN003'),
(1, 'commission', 'Affiliate', 25000, 'completed', 'Komisi affiliate dari link shopee-fashion-2024', 'TXN004'),
(1, 'deposit', 'Kartu Kredit', 1000000, 'pending', 'Deposit via Kartu Kredit', 'TXN005');

INSERT INTO affiliate_history (user_id, link_name, link_url, platform, amount, commission_rate, status, clicks, conversions) VALUES
(1, 'shopee-fashion-2024', 'https://shopee.co.id/fashion-sale', 'shopee', 25000, 5.0, 'approved', 45, 3),
(1, 'tiktok-gadget-promo', 'https://tiktok.com/gadget-promo', 'tiktok', 15000, 4.5, 'paid', 32, 2),
(1, 'lazada-electronics', 'https://lazada.co.id/electronics', 'lazada', 35000, 6.0, 'pending', 50, 4);

INSERT INTO smm_history (user_id, service_id, service_name, platform, description, target_url, quantity, amount, status, start_count, current_count, remains, progress_percentage) VALUES
(1, 'SLV001', 'Shopee Live Views', 'shopee', '1.000 views untuk live stream', 'https://live.shopee.co.id/12345', 1000, 50000, 'completed', 0, 1000, 0, 100.00),
(1, 'TLV001', 'TikTok Live Views', 'tiktok', '500 views untuk live stream', 'https://tiktok.com/@username/live', 500, 75000, 'processing', 0, 350, 150, 70.00),
(1, 'IGF001', 'Instagram Followers', 'instagram', '2.000 followers', 'https://instagram.com/username', 2000, 150000, 'completed', 1500, 2000, 0, 100.00),
(1, 'YTV001', 'YouTube Views', 'youtube', '5.000 views untuk video', 'https://youtube.com/watch?v=abc123', 5000, 25000, 'pending', 1200, 0, 5000, 0.00);

-- Create triggers for automatic updates
DELIMITER //

CREATE TRIGGER update_smm_progress 
BEFORE UPDATE ON smm_history
FOR EACH ROW
BEGIN
    IF NEW.quantity > 0 THEN
        SET NEW.progress_percentage = ROUND((NEW.current_count / NEW.quantity) * 100, 2);
        SET NEW.remains = NEW.quantity - NEW.current_count;
    END IF;
    
    IF NEW.current_count >= NEW.quantity AND NEW.status = 'processing' THEN
        SET NEW.status = 'completed';
        SET NEW.completion_date = CURRENT_TIMESTAMP;
    END IF;
END//

CREATE TRIGGER log_transaction_activity
AFTER INSERT ON transaction_history
FOR EACH ROW
BEGIN
    INSERT INTO activity_log (user_id, activity_type, activity_description, metadata)
    VALUES (
        NEW.user_id, 
        'transaction_created', 
        CONCAT('New ', NEW.type, ' transaction created: ', NEW.description),
        JSON_OBJECT('transaction_id', NEW.id, 'type', NEW.type, 'amount', NEW.amount)
    );
END//

CREATE TRIGGER log_affiliate_activity
AFTER INSERT ON affiliate_history
FOR EACH ROW
BEGIN
    INSERT INTO activity_log (user_id, activity_type, activity_description, metadata)
    VALUES (
        NEW.user_id, 
        'affiliate_commission', 
        CONCAT('New affiliate commission from link: ', NEW.link_name),
        JSON_OBJECT('commission_id', NEW.id, 'link', NEW.link_name, 'amount', NEW.amount)
    );
END//

CREATE TRIGGER log_smm_activity
AFTER INSERT ON smm_history
FOR EACH ROW
BEGIN
    INSERT INTO activity_log (user_id, activity_type, activity_description, metadata)
    VALUES (
        NEW.user_id, 
        'smm_order_created', 
        CONCAT('New SMM order created: ', NEW.service_name),
        JSON_OBJECT('order_id', NEW.id, 'service', NEW.service_name, 'platform', NEW.platform, 'amount', NEW.amount)
    );
END//

DELIMITER ;

-- Create stored procedures for common operations
DELIMITER //

CREATE PROCEDURE GetUserTransactionSummary(IN p_user_id INT)
BEGIN
    SELECT 
        COUNT(*) as total_transactions,
        SUM(CASE WHEN type = 'deposit' AND status = 'completed' THEN amount ELSE 0 END) as total_deposits,
        SUM(CASE WHEN type = 'withdrawal' AND status = 'completed' THEN amount ELSE 0 END) as total_withdrawals,
        SUM(CASE WHEN type = 'commission' AND status = 'completed' THEN amount ELSE 0 END) as total_commissions,
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_transactions,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_transactions,
        COUNT(CASE WHEN status = 'failed' THEN 1 END) as failed_transactions
    FROM transaction_history 
    WHERE user_id = p_user_id;
END//

CREATE PROCEDURE GetUserAffiliateSummary(IN p_user_id INT)
BEGIN
    SELECT 
        COUNT(*) as total_commissions,
        COUNT(DISTINCT link_name) as total_links,
        SUM(clicks) as total_clicks,
        SUM(conversions) as total_conversions,
        SUM(CASE WHEN status IN ('approved', 'paid') THEN amount ELSE 0 END) as total_earnings,
        CASE 
            WHEN SUM(clicks) > 0 THEN ROUND((SUM(conversions) / SUM(clicks)) * 100, 2)
            ELSE 0 
        END as conversion_rate,
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_commissions,
        COUNT(CASE WHEN status = 'approved' THEN 1 END) as approved_commissions,
        COUNT(CASE WHEN status = 'paid' THEN 1 END) as paid_commissions
    FROM affiliate_history 
    WHERE user_id = p_user_id;
END//

CREATE PROCEDURE GetUserSmmSummary(IN p_user_id INT)
BEGIN
    SELECT 
        COUNT(*) as total_orders,
        SUM(amount) as total_spent,
        COUNT(CASE WHEN status = 'completed' THEN 1 END) as completed_orders,
        COUNT(CASE WHEN status = 'processing' THEN 1 END) as processing_orders,
        COUNT(CASE WHEN status = 'pending' THEN 1 END) as pending_orders,
        COUNT(CASE WHEN status = 'cancelled' THEN 1 END) as cancelled_orders,
        AVG(progress_percentage) as avg_progress,
        COUNT(DISTINCT platform) as platforms_used
    FROM smm_history 
    WHERE user_id = p_user_id;
END//

CREATE PROCEDURE UpdateSmmProgress(
    IN p_order_id INT,
    IN p_current_count INT
)
BEGIN
    DECLARE v_quantity INT;
    DECLARE v_progress DECIMAL(5,2);
    
    -- Get the quantity for this order
    SELECT quantity INTO v_quantity FROM smm_history WHERE id = p_order_id;
    
    -- Calculate progress percentage
    SET v_progress = ROUND((p_current_count / v_quantity) * 100, 2);
    
    -- Update the order
    UPDATE smm_history 
    SET 
        current_count = p_current_count,
        remains = v_quantity - p_current_count,
        progress_percentage = v_progress,
        status = CASE 
            WHEN p_current_count >= v_quantity THEN 'completed'
            WHEN p_current_count > 0 THEN 'processing'
            ELSE status
        END,
        completion_date = CASE 
            WHEN p_current_count >= v_quantity THEN CURRENT_TIMESTAMP
            ELSE completion_date
        END
    WHERE id = p_order_id;
END//

DELIMITER ;

-- Create indexes for better performance
CREATE INDEX idx_transaction_history_composite ON transaction_history (user_id, type, status, created_at);
CREATE INDEX idx_affiliate_history_composite ON affiliate_history (user_id, platform, status, created_at);
CREATE INDEX idx_smm_history_composite ON smm_history (user_id, platform, status, created_at);
CREATE INDEX idx_wallet_history_composite ON wallet_history (user_id, transaction_type, source, created_at);
CREATE INDEX idx_activity_log_composite ON activity_log (user_id, activity_type, created_at);

-- Add comments to tables
ALTER TABLE transaction_history COMMENT = 'Stores all financial transactions including deposits, withdrawals, purchases, commissions, and refunds';
ALTER TABLE affiliate_history COMMENT = 'Stores affiliate commission history and performance metrics';
ALTER TABLE smm_history COMMENT = 'Stores SMM (Social Media Marketing) order history and progress tracking';
ALTER TABLE wallet_history COMMENT = 'Stores Go-Coins wallet transaction history for balance tracking';
ALTER TABLE activity_log COMMENT = 'Stores user activity logs for audit and tracking purposes';
ALTER TABLE notification_history COMMENT = 'Stores user notifications and their read status';
