-- SQL untuk integrasi dengan sistem performance yang sudah ada
-- Jalankan script ini untuk menghubungkan link generator dengan data performance Anda

-- 1. Buat tabel untuk link generator yang terintegrasi dengan sistem existing
CREATE TABLE IF NOT EXISTS `generator_users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` VARCHAR(50) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `name` VARCHAR(100),
    `email` VARCHAR(100),
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_by` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `last_login` TIMESTAMP NULL
);

-- 2. Buat tabel campaigns yang kompatibel dengan sistem existing
CREATE TABLE IF NOT EXISTS `campaigns` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `smart_link` TEXT NOT NULL,
    `postback_url` TEXT,
    `user_id` INT,
    `status` ENUM('active', 'inactive', 'paused') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `generator_users`(`id`)
);

-- 3. Buat tabel generated_links untuk tracking
CREATE TABLE IF NOT EXISTS `generated_links` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `short_code` VARCHAR(20) UNIQUE NOT NULL,
    `original_url` TEXT NOT NULL,
    `campaign_id` INT,
    `user_id` INT,
    `domain_id` INT,
    `clicks` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP NULL,
    `status` ENUM('active', 'inactive', 'expired') DEFAULT 'active',
    FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`),
    FOREIGN KEY (`user_id`) REFERENCES `generator_users`(`id`)
);

-- 4. Buat tabel click_tracking untuk detail tracking
CREATE TABLE IF NOT EXISTS `click_tracking` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `link_id` INT NOT NULL,
    `ip_address` VARCHAR(45),
    `user_agent` TEXT,
    `country` VARCHAR(2),
    `city` VARCHAR(100),
    `referrer` TEXT,
    `clicked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`link_id`) REFERENCES `generated_links`(`id`)
);

-- 5. Buat tabel conversions yang terintegrasi dengan existing system
CREATE TABLE IF NOT EXISTS `conversions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `date` DATE NOT NULL,
    `click_id` BIGINT(20),
    `user_id` INT,
    `campaign_id` INT,
    `sub1` VARCHAR(12),
    `sub2` VARCHAR(12),
    `country` VARCHAR(4),
    `payout` DOUBLE NOT NULL,
    `network` VARCHAR(50),
    `os` VARCHAR(16),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `generator_users`(`id`),
    FOREIGN KEY (`campaign_id`) REFERENCES `campaigns`(`id`)
);

-- 6. Insert admin user default
INSERT IGNORE INTO `generator_users` (`user_id`, `password`, `name`, `email`, `status`) 
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrator', 'admin@linkgen.com', 'active');

-- 7. Insert sample campaign
INSERT IGNORE INTO `campaigns` (`name`, `smart_link`, `postback_url`, `user_id`) 
VALUES ('Sample Campaign', 'https://partner.imonetizeit.com/smartlink', 'https://yourdomain.com/postback.php', 1);

-- 8. Create views untuk integrasi dengan existing data
CREATE OR REPLACE VIEW `performance_summary` AS
SELECT 
    lr.sub1 as user_id,
    lr.name as campaign_name,
    lr.date,
    SUM(lr.hits) as hits,
    SUM(lr.uniques) as uniques,
    SUM(lr.clicks) as clicks,
    SUM(lr.conversions) as conversions,
    SUM(lr.earnings) as earnings,
    AVG(lr.cr) as conversion_rate
FROM `link_report` lr
GROUP BY lr.sub1, lr.name, lr.date
ORDER BY lr.date DESC;

-- 9. Create view untuk dashboard statistics
CREATE OR REPLACE VIEW `user_performance` AS
SELECT 
    gu.user_id,
    gu.name as user_name,
    COUNT(DISTINCT gl.id) as total_links,
    SUM(gl.clicks) as total_clicks,
    COUNT(DISTINCT c.id) as total_conversions,
    SUM(c.payout) as total_earnings,
    MAX(gl.created_at) as last_activity
FROM `generator_users` gu
LEFT JOIN `generated_links` gl ON gu.id = gl.user_id
LEFT JOIN `conversions` c ON gu.id = c.user_id
WHERE gu.status = 'active'
GROUP BY gu.id, gu.user_id, gu.name;

-- 10. Create stored procedure untuk sync data dari existing tables
DELIMITER //
CREATE PROCEDURE SyncPerformanceData()
BEGIN
    -- Sync data dari link_report ke conversions table
    INSERT INTO conversions (date, user_id, sub1, country, payout, network, created_at)
    SELECT 
        lr.date,
        (SELECT id FROM generator_users WHERE user_id = lr.sub1 LIMIT 1) as user_id,
        lr.sub1,
        lr.country,
        lr.earnings,
        lr.network,
        NOW()
    FROM link_report lr
    LEFT JOIN conversions c ON lr.date = c.date AND lr.sub1 = c.sub1
    WHERE c.id IS NULL AND lr.earnings > 0;
    
    -- Update clicks pada generated_links dari existing data
    UPDATE generated_links gl
    JOIN (
        SELECT sub1, SUM(clicks) as total_clicks
        FROM link_report 
        GROUP BY sub1
    ) lr ON (SELECT user_id FROM generator_users WHERE id = gl.user_id) = lr.sub1
    SET gl.clicks = lr.total_clicks;
END //
DELIMITER ;

-- 11. Create function untuk generate tracking links
DELIMITER //
CREATE FUNCTION GenerateTrackingCode(length INT) RETURNS VARCHAR(20)
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE chars VARCHAR(62) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE result VARCHAR(20) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    DECLARE pos INT;
    
    WHILE i < length DO
        SET pos = FLOOR(1 + RAND() * 62);
        SET result = CONCAT(result, SUBSTRING(chars, pos, 1));
        SET i = i + 1;
    END WHILE;
    
    RETURN result;
END //
DELIMITER ;

-- 12. Create triggers untuk auto-update statistics
DELIMITER //
CREATE TRIGGER update_link_clicks AFTER INSERT ON click_tracking
FOR EACH ROW
BEGIN
    UPDATE generated_links 
    SET clicks = clicks + 1 
    WHERE id = NEW.link_id;
END //
DELIMITER ;

-- 13. Create indexes untuk performance
CREATE INDEX idx_link_report_date ON link_report(date);
CREATE INDEX idx_link_report_sub1 ON link_report(sub1);
CREATE INDEX idx_conversions_date ON conversions(date);
CREATE INDEX idx_conversions_user ON conversions(user_id);
CREATE INDEX idx_generated_links_user ON generated_links(user_id);
CREATE INDEX idx_click_tracking_link ON click_tracking(link_id);

-- 14. Sample data untuk testing
INSERT IGNORE INTO `generator_users` (`user_id`, `password`, `name`, `email`, `created_by`) VALUES 
('user001', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test User 1', 'user1@test.com', 1),
('user002', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Test User 2', 'user2@test.com', 1);

-- 15. Insert sample domains
INSERT IGNORE INTO `domains` (`name`, `status`) VALUES 
('short.ly', 'active'),
('link.co', 'active'),
('go.link', 'active');

-- Jalankan sync procedure untuk pertama kali
CALL SyncPerformanceData();

-- Query untuk cek hasil integrasi
SELECT 'Integration completed successfully!' as status;
SELECT COUNT(*) as total_users FROM generator_users;
SELECT COUNT(*) as total_campaigns FROM campaigns;
SELECT COUNT(*) as total_performance_records FROM link_report;