-- Adexora Database Schema
-- Created: December 2024

DROP DATABASE IF EXISTS adexora_db;
CREATE DATABASE adexora_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE adexora_db;

-- Publishers Table
CREATE TABLE publishers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(15) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    telegram_username VARCHAR(100),
    app_category ENUM('entertainment', 'education', 'utility', 'game', 'business', 'news', 'other') NOT NULL,
    expected_traffic ENUM('0-1000', '1000-5000', '5000-10000', '10000+') DEFAULT '0-1000',
    monetag_jun_id VARCHAR(100) UNIQUE,
    verification_token VARCHAR(100),
    reset_token VARCHAR(100),
    reset_token_expires DATETIME,
    status ENUM('pending', 'active', 'suspended', 'rejected') DEFAULT 'pending',
    balance DECIMAL(12,2) DEFAULT 0.00,
    total_earnings DECIMAL(12,2) DEFAULT 0.00,
    last_payment_date DATETIME,
    last_login DATETIME,
    verified_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
);

-- Advertisers Table
CREATE TABLE advertisers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_name VARCHAR(255) NOT NULL,
    contact_name VARCHAR(255) NOT NULL,
    contact_email VARCHAR(255) UNIQUE NOT NULL,
    contact_phone VARCHAR(15) NOT NULL,
    business_type ENUM('ecommerce', 'service', 'education', 'entertainment', 'other') NOT NULL,
    website VARCHAR(500),
    monthly_budget DECIMAL(10,2),
    target_audience VARCHAR(100),
    password VARCHAR(255) NOT NULL,
    status ENUM('pending', 'active', 'suspended') DEFAULT 'pending',
    balance DECIMAL(12,2) DEFAULT 0.00,
    total_spent DECIMAL(12,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Sites Table (Telegram Mini Apps & Websites)
CREATE TABLE sites (
    id INT PRIMARY KEY AUTO_INCREMENT,
    publisher_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    url VARCHAR(500) NOT NULL,
    type ENUM('telegram', 'website', 'blog', 'app') DEFAULT 'telegram',
    category ENUM('entertainment', 'education', 'utility', 'game', 'business', 'news', 'other') NOT NULL,
    description TEXT,
    status ENUM('pending', 'active', 'suspended', 'rejected') DEFAULT 'pending',
    verification_code VARCHAR(100),
    monthly_traffic INT DEFAULT 0,
    screenshot_path VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (publisher_id) REFERENCES publishers(id) ON DELETE CASCADE,
    INDEX idx_publisher_id (publisher_id),
    INDEX idx_status (status),
    UNIQUE KEY unique_site_url (publisher_id, url)
);

-- Ad Units Table
CREATE TABLE ad_units (
    id INT PRIMARY KEY AUTO_INCREMENT,
    publisher_id INT NOT NULL,
    site_id INT NOT NULL,
    monetag_ad_unit_id VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(255) NOT NULL,
    type ENUM('banner', 'interstitial', 'rewarded', 'native') NOT NULL,
    placement VARCHAR(100),
    dimensions VARCHAR(50),
    status ENUM('active', 'paused', 'pending') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (publisher_id) REFERENCES publishers(id) ON DELETE CASCADE,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    INDEX idx_publisher_id (publisher_id),
    INDEX idx_site_id (site_id),
    INDEX idx_status (status)
);

-- Earnings History Table
CREATE TABLE earnings_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    publisher_id INT NOT NULL,
    site_id INT NOT NULL,
    ad_unit_id INT NOT NULL,
    date DATE NOT NULL,
    impressions INT DEFAULT 0,
    clicks INT DEFAULT 0,
    ctr DECIMAL(5,2) DEFAULT 0.00,
    earnings DECIMAL(10,4) DEFAULT 0.0000, -- Total earnings from Monetag
    publisher_share DECIMAL(10,4) DEFAULT 0.0000, -- 70% of earnings
    monetag_jun_id VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (publisher_id) REFERENCES publishers(id) ON DELETE CASCADE,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    FOREIGN KEY (ad_unit_id) REFERENCES ad_units(id) ON DELETE CASCADE,
    UNIQUE KEY unique_daily_earnings (publisher_id, site_id, ad_unit_id, date),
    INDEX idx_date (date),
    INDEX idx_publisher_date (publisher_id, date)
);

-- Payments Table
CREATE TABLE payments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    publisher_id INT NOT NULL,
    payment_method ENUM('bkash', 'nagad', 'bank', 'rocket') NOT NULL,
    method_details JSON, -- Store account numbers, bank info etc.
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    transaction_id VARCHAR(100),
    admin_notes TEXT,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at DATETIME,
    
    FOREIGN KEY (publisher_id) REFERENCES publishers(id) ON DELETE CASCADE,
    INDEX idx_publisher_id (publisher_id),
    INDEX idx_status (status),
    INDEX idx_requested_at (requested_at)
);

-- Ad Campaigns Table
CREATE TABLE ad_campaigns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    advertiser_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    budget DECIMAL(10,2) NOT NULL,
    daily_budget DECIMAL(10,2),
    target_audience JSON,
    ad_creative_path VARCHAR(500),
    landing_page_url VARCHAR(500),
    status ENUM('pending', 'active', 'paused', 'completed', 'rejected') DEFAULT 'pending',
    start_date DATE,
    end_date DATE,
    impressions INT DEFAULT 0,
    clicks INT DEFAULT 0,
    spent DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (advertiser_id) REFERENCES advertisers(id) ON DELETE CASCADE,
    INDEX idx_advertiser_id (advertiser_id),
    INDEX idx_status (status)
);

-- Admin Users Table
CREATE TABLE admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    role ENUM('super_admin', 'admin', 'moderator') DEFAULT 'admin',
    permissions JSON,
    last_login DATETIME,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- System Settings Table
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type ENUM('string', 'number', 'boolean', 'json') DEFAULT 'string',
    description TEXT,
    updated_by INT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (updated_by) REFERENCES admin_users(id) ON DELETE SET NULL
);

-- Activity Logs Table
CREATE TABLE activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    user_type ENUM('publisher', 'advertiser', 'admin') NOT NULL,
    action VARCHAR(100) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);

-- Notifications Table
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    user_type ENUM('publisher', 'advertiser', 'admin') NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'success', 'warning', 'error') DEFAULT 'info',
    is_read BOOLEAN DEFAULT FALSE,
    related_id INT, -- Related entity ID
    related_type VARCHAR(50), -- Related entity type
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user (user_id, user_type),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at)
);

-- Telegram Users Table (for Mini Apps integration)
CREATE TABLE telegram_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    language_code VARCHAR(10),
    is_premium BOOLEAN DEFAULT FALSE,
    last_activity DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_telegram_id (telegram_id),
    INDEX idx_username (username)
);

-- Telegram Ad Interactions Table
CREATE TABLE telegram_ad_interactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    telegram_user_id BIGINT NOT NULL,
    publisher_id INT NOT NULL,
    ad_unit_id INT NOT NULL,
    interaction_type ENUM('impression', 'click') NOT NULL,
    revenue DECIMAL(8,4) DEFAULT 0.0000,
    interaction_data JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_telegram_user (telegram_user_id),
    INDEX idx_publisher (publisher_id),
    INDEX idx_created_at (created_at)
);

-- API Logs Table
CREATE TABLE api_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    endpoint VARCHAR(255) NOT NULL,
    method VARCHAR(10) NOT NULL,
    request_data JSON,
    response_data JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    response_code INT,
    duration_ms INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_endpoint (endpoint),
    INDEX idx_created_at (created_at)
);

-- Insert Default Admin User
INSERT INTO admin_users (username, email, password, full_name, role, status) VALUES
('superadmin', 'admin@adexora.com', '$2y$12$hashed_password_here', 'Super Administrator', 'super_admin', 'active');

-- Insert Default System Settings
INSERT INTO system_settings (setting_key, setting_value, setting_type, description) VALUES
('site_name', 'Adexora', 'string', 'Website name'),
('site_url', 'https://adexora.com', 'string', 'Website URL'),
('site_email', 'support@adexora.com', 'string', 'Support email'),
('payout_rate', '0.70', 'number', 'Publisher payout rate (70%)'),
('minimum_payout', '500', 'number', 'Minimum payout amount'),
('currency', 'BDT', 'string', 'Default currency'),
('telegram_bot_token', 'your_bot_token_here', 'string', 'Telegram Bot Token'),
('monetag_api_key', 'your_monetag_api_key', 'string', 'Monetag API Key'),
('monetag_jun_id', 'your_jun_id', 'string', 'Monetag JUN ID');

-- Create Views for Reporting
CREATE VIEW publisher_dashboard_stats AS
SELECT 
    p.id as publisher_id,
    p.name as publisher_name,
    COUNT(DISTINCT s.id) as total_sites,
    COUNT(DISTINCT au.id) as total_ad_units,
    COALESCE(SUM(eh.impressions), 0) as total_impressions,
    COALESCE(SUM(eh.clicks), 0) as total_clicks,
    CASE 
        WHEN COALESCE(SUM(eh.impressions), 0) > 0 
        THEN ROUND((COALESCE(SUM(eh.clicks), 0) / COALESCE(SUM(eh.impressions), 0)) * 100, 2)
        ELSE 0 
    END as overall_ctr,
    COALESCE(SUM(eh.publisher_share), 0) as total_earnings,
    p.balance as current_balance
FROM publishers p
LEFT JOIN sites s ON p.id = s.publisher_id AND s.status = 'active'
LEFT JOIN ad_units au ON s.id = au.site_id AND au.status = 'active'
LEFT JOIN earnings_history eh ON p.id = eh.publisher_id
WHERE p.status = 'active'
GROUP BY p.id, p.name, p.balance;

CREATE VIEW daily_earnings_report AS
SELECT 
    date,
    COUNT(DISTINCT publisher_id) as active_publishers,
    SUM(impressions) as total_impressions,
    SUM(clicks) as total_clicks,
    ROUND((SUM(clicks) / NULLIF(SUM(impressions), 0)) * 100, 2) as overall_ctr,
    SUM(earnings) as total_earnings,
    SUM(publisher_share) as total_publisher_share
FROM earnings_history
GROUP BY date
ORDER BY date DESC;

-- Create Stored Procedures
DELIMITER //

CREATE PROCEDURE ProcessPayouts(IN min_amount DECIMAL(10,2))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE publisher_id INT;
    DECLARE publisher_balance DECIMAL(10,2);
    DECLARE payment_method VARCHAR(50);
    DECLARE method_details JSON;
    
    -- Cursor for publishers with sufficient balance
    DECLARE cur CURSOR FOR 
        SELECT id, balance, payment_method, payment_details 
        FROM publishers 
        WHERE balance >= min_amount AND status = 'active';
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO publisher_id, publisher_balance, payment_method, method_details;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Insert payment record
        INSERT INTO payments (publisher_id, payment_method, method_details, amount, status)
        VALUES (publisher_id, payment_method, method_details, publisher_balance, 'processing');
        
        -- Reset publisher balance
        UPDATE publishers 
        SET balance = 0, last_payment_date = NOW() 
        WHERE id = publisher_id;
        
    END LOOP;
    
    CLOSE cur;
END//

CREATE PROCEDURE UpdateDailyEarnings()
BEGIN
    -- This would be called by a cron job to update earnings from Monetag API
    -- For now, it's a placeholder for the actual implementation
    
    INSERT INTO api_logs (endpoint, method, response_code, created_at)
    VALUES ('/cron/update-earnings', 'GET', 200, NOW());
END//

DELIMITER ;


-- Create Database User (Optional - for production)
-- CREATE USER 'adexora_user'@'localhost' IDENTIFIED BY 'secure_password_here';
-- GRANT ALL PRIVILEGES ON adexora_db.* TO 'adexora_user'@'localhost';
-- FLUSH PRIVILEGES;