-- =====================================================================
--  ArNi Solutions CRM – Affiliate-Modul (ADCELL & generisch)
--  Prefix crm_ (geteilte DB) · utf8mb4 · InnoDB
-- =====================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- Netzwerke (z.B. ADCELL) – Zugangsdaten/Endpunkte
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_affiliate_networks` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(120) NOT NULL,
  `type`         VARCHAR(40) NOT NULL DEFAULT 'adcell',
  `api_base_url` VARCHAR(255) NULL,
  `api_key`      VARCHAR(255) NULL,
  `api_secret`   VARCHAR(255) NULL,
  `publisher_id` VARCHAR(80) NULL,
  `username`     VARCHAR(120) NULL,
  `password`     VARCHAR(255) NULL,     -- optional; bei Bedarf verschlüsselt ablegen
  `auth_type`    ENUM('api_key','basic_auth','token') NOT NULL DEFAULT 'api_key',
  `sync_interval`INT NOT NULL DEFAULT 30,  -- Minuten
  `is_active`    TINYINT(1) NOT NULL DEFAULT 1,
  `last_sync_at` DATETIME NULL,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_net_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Partnerprogramme
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_affiliate_programs` (
  `id`                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `network_id`          INT UNSIGNED NOT NULL,
  `external_program_id` VARCHAR(80) NOT NULL,
  `name`                VARCHAR(190) NOT NULL,
  `advertiser_name`     VARCHAR(190) NULL,
  `category`            VARCHAR(120) NULL,
  `status`              VARCHAR(40) NULL,
  `commission_model`    VARCHAR(190) NULL,
  `commission_value`    TEXT NULL,            -- frei (z.B. ADCELL commissionInformation, oft langer Text)
  `approval_status`     VARCHAR(40) NULL,
  `program_url`         VARCHAR(255) NULL,
  `tracking_base_url`   VARCHAR(255) NULL,
  `description`         TEXT NULL,
  `logo_url`            VARCHAR(255) NULL,
  `is_active`           TINYINT(1) NOT NULL DEFAULT 1,
  `last_sync_at`        DATETIME NULL,
  `created_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_prog_ext` (`network_id`,`external_program_id`),
  KEY `idx_prog_cat` (`category`),
  KEY `idx_prog_status` (`status`),
  CONSTRAINT `fk_prog_net` FOREIGN KEY (`network_id`) REFERENCES `crm_affiliate_networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Werbemittel
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_affiliate_creatives` (
  `id`                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `network_id`           INT UNSIGNED NOT NULL,
  `program_id`           INT UNSIGNED NULL,
  `external_creative_id` VARCHAR(80) NOT NULL,
  `creative_type`        VARCHAR(40) NULL,    -- banner|textlink|html|rss|csv
  `name`                 VARCHAR(190) NULL,
  `format`               VARCHAR(80) NULL,
  `width`                INT NULL,
  `height`               INT NULL,
  `target_url`           VARCHAR(500) NULL,
  `tracking_url`         VARCHAR(500) NULL,
  `image_url`            VARCHAR(500) NULL,
  `html_code`            MEDIUMTEXT NULL,
  `text_code`            TEXT NULL,
  `csv_url`              VARCHAR(500) NULL,
  `rss_url`              VARCHAR(500) NULL,
  `status`               VARCHAR(40) NULL,
  `is_active`            TINYINT(1) NOT NULL DEFAULT 1,
  `last_sync_at`         DATETIME NULL,
  `created_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_creative_ext` (`network_id`,`external_creative_id`),
  KEY `idx_cr_program` (`program_id`),
  KEY `idx_cr_type` (`creative_type`),
  CONSTRAINT `fk_cr_net` FOREIGN KEY (`network_id`) REFERENCES `crm_affiliate_networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Statistiken (Tageswerte; program_id/creative_id optional)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_affiliate_statistics` (
  `id`                   INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `network_id`           INT UNSIGNED NOT NULL,
  `program_id`           INT UNSIGNED NULL,
  `creative_id`          INT UNSIGNED NULL,
  `date`                 DATE NOT NULL,
  `month`                TINYINT UNSIGNED NOT NULL,
  `year`                 SMALLINT UNSIGNED NOT NULL,
  `views`                INT NOT NULL DEFAULT 0,
  `clicks`               INT NOT NULL DEFAULT 0,
  `leads`                INT NOT NULL DEFAULT 0,
  `sales`                INT NOT NULL DEFAULT 0,
  `open_commission`      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `confirmed_commission` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `cancelled_commission` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `total_commission`     DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `conversion_rate`      DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
  `click_through_rate`   DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
  `cancellation_rate`    DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
  `created_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_stat_net_date` (`network_id`,`date`),
  KEY `idx_stat_ym` (`year`,`month`),
  KEY `idx_stat_program` (`program_id`),
  KEY `idx_stat_creative` (`creative_id`),
  CONSTRAINT `fk_stat_net` FOREIGN KEY (`network_id`) REFERENCES `crm_affiliate_networks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Sync-Protokoll
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_affiliate_sync_logs` (
  `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `network_id`      INT UNSIGNED NULL,
  `sync_type`       VARCHAR(40) NOT NULL,    -- programs|creatives|statistics|test|import
  `status`          ENUM('success','error','partial','running') NOT NULL DEFAULT 'running',
  `message`         VARCHAR(255) NULL,
  `records_found`   INT NOT NULL DEFAULT 0,
  `records_created` INT NOT NULL DEFAULT 0,
  `records_updated` INT NOT NULL DEFAULT 0,
  `error_payload`   TEXT NULL,               -- ohne Secrets
  `started_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `finished_at`     DATETIME NULL,
  PRIMARY KEY (`id`),
  KEY `idx_synclog_net` (`network_id`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Verknüpfung Webseite ↔ Partnerprogramm (Tags)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `crm_website_programs` (
  `website_id` INT UNSIGNED NOT NULL,
  `program_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`website_id`,`program_id`),
  KEY `idx_wp_program` (`program_id`),
  CONSTRAINT `fk_wp_site` FOREIGN KEY (`website_id`) REFERENCES `crm_websites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;