

-- 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`)
);

-- 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;
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);

-- 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_campaigns FROM campaigns;
SELECT COUNT(*) as total_performance_records FROM link_report;