CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_pageviews` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `page_url` VARCHAR(1024) NOT NULL DEFAULT '',
    `page_title` VARCHAR(500) NOT NULL DEFAULT '',
    `referrer_url` VARCHAR(1024) DEFAULT NULL,
    `referrer_domain` VARCHAR(255) DEFAULT NULL,
    `visitor_hash` VARCHAR(64) NOT NULL DEFAULT '',
    `device_type` VARCHAR(20) DEFAULT NULL,
    `browser` VARCHAR(100) DEFAULT NULL,
    `os` VARCHAR(100) DEFAULT NULL,
    `country_code` CHAR(2) DEFAULT NULL,
    `session_id` VARCHAR(64) DEFAULT NULL,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_mokosuiteanalytics_pv_visitor` (`visitor_hash`),
    KEY `idx_mokosuiteanalytics_pv_created` (`created_at`),
    KEY `idx_mokosuiteanalytics_pv_url` (`page_url`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_visitors` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `visitor_hash` VARCHAR(64) NOT NULL,
    `first_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `last_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `pageview_count` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_mokosuiteanalytics_visitors_hash` (`visitor_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_campaigns` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL DEFAULT '',
    `source` VARCHAR(100) DEFAULT NULL,
    `medium` VARCHAR(100) DEFAULT NULL,
    `campaign_code` VARCHAR(100) DEFAULT NULL,
    `click_count` INT NOT NULL DEFAULT 0,
    `conversion_count` INT NOT NULL DEFAULT 0,
    `published` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_ad_accounts` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `platform` VARCHAR(50) NOT NULL DEFAULT '',
    `account_id` VARCHAR(255) NOT NULL DEFAULT '',
    `api_credentials` TEXT COMMENT 'Encrypted JSON',
    `sync_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `last_sync` DATETIME DEFAULT NULL,
    `published` TINYINT(1) NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_ad_metrics` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ad_account_id` INT UNSIGNED NOT NULL,
    `date` DATE NOT NULL,
    `impressions` INT NOT NULL DEFAULT 0,
    `clicks` INT NOT NULL DEFAULT 0,
    `conversions` INT NOT NULL DEFAULT 0,
    `spend` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `revenue` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `campaign_name` VARCHAR(255) DEFAULT NULL,
    `ad_group_name` VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_mokosuiteanalytics_adm_account_date` (`ad_account_id`, `date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__mokosuiteanalytics_reports` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL DEFAULT '',
    `config` TEXT COMMENT 'JSON report configuration',
    `schedule` VARCHAR(20) NOT NULL DEFAULT 'none',
    `email_recipients` TEXT,
    `created_by` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 DEFAULT COLLATE=utf8mb4_unicode_ci;