-- nn_ad_click_log — partitioned click tracking table
-- Partition key must be part of PRIMARY KEY in MariaDB
CREATE TABLE IF NOT EXISTS nn_ad_click_log (
  id               BIGINT UNSIGNED AUTO_INCREMENT,
  link_id          INT UNSIGNED NOT NULL,
  campaign_id      INT UNSIGNED DEFAULT NULL,
  session_hash     CHAR(64) NOT NULL,
  ip_address       VARCHAR(45) DEFAULT NULL,
  ip_hash          CHAR(64) DEFAULT NULL,
  user_agent       TEXT DEFAULT NULL,
  device_type      ENUM('mobile','tablet','desktop','bot') DEFAULT NULL,
  browser          VARCHAR(50) DEFAULT NULL,
  os               VARCHAR(50) DEFAULT NULL,
  country          VARCHAR(2) DEFAULT NULL,
  region           VARCHAR(50) DEFAULT NULL,
  city             VARCHAR(50) DEFAULT NULL,
  referrer_url     VARCHAR(500) DEFAULT NULL,
  referrer_domain  VARCHAR(100) DEFAULT NULL,
  utm_source       VARCHAR(50) DEFAULT NULL,
  utm_medium       VARCHAR(50) DEFAULT NULL,
  utm_campaign     VARCHAR(100) DEFAULT NULL,
  utm_content      VARCHAR(100) DEFAULT NULL,
  converted_at     DATETIME DEFAULT NULL,
  conversion_value DECIMAL(12,2) DEFAULT NULL,
  order_id         INT UNSIGNED DEFAULT NULL,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id, created_at),
  KEY idx_link (link_id, created_at),
  KEY idx_session (session_hash),
  KEY idx_conversion (converted_at, conversion_value),
  KEY idx_geo (country, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p0 VALUES LESS THAN (TO_DAYS('2026-06-01')),
  PARTITION p1 VALUES LESS THAN (TO_DAYS('2026-07-01')),
  PARTITION p2 VALUES LESS THAN (TO_DAYS('2026-08-01')),
  PARTITION p3 VALUES LESS THAN (TO_DAYS('2026-09-01')),
  PARTITION p4 VALUES LESS THAN (TO_DAYS('2026-10-01')),
  PARTITION p5 VALUES LESS THAN MAXVALUE
);

-- nn_revenue_attribution — connects orders to ad clicks
CREATE TABLE IF NOT EXISTS nn_revenue_attribution (
  id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id           INT UNSIGNED NOT NULL,
  click_id           BIGINT UNSIGNED NOT NULL,
  link_id            INT UNSIGNED NOT NULL,
  campaign_id        INT UNSIGNED NOT NULL,
  account_id         INT UNSIGNED NOT NULL,
  platform_id        INT UNSIGNED NOT NULL,
  attribution_model  ENUM('first_click','last_click','linear','time_decay') NOT NULL DEFAULT 'last_click',
  attribution_weight DECIMAL(3,2) NOT NULL DEFAULT 1.00,
  order_subtotal     DECIMAL(10,2) NOT NULL,
  attributed_revenue DECIMAL(10,2) NOT NULL,
  created_at         DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_order_click (order_id, click_id),
  KEY idx_campaign (campaign_id, attributed_revenue),
  KEY idx_account (account_id, attributed_revenue),
  KEY idx_platform (platform_id, attributed_revenue)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- nn_ad_spend_daily — budget automation log (merged with previous if exists)
CREATE TABLE IF NOT EXISTS nn_ad_spend_daily (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id   INT UNSIGNED NOT NULL,
  platform_id  INT UNSIGNED NOT NULL,
  spend_date   DATE NOT NULL,
  spend_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  impressions  INT UNSIGNED NOT NULL DEFAULT 0,
  clicks       INT UNSIGNED NOT NULL DEFAULT 0,
  conversions  INT UNSIGNED NOT NULL DEFAULT 0,
  revenue      DECIMAL(12,2) NOT NULL DEFAULT 0,
  roas         DECIMAL(5,2) DEFAULT NULL,
  cpa          DECIMAL(10,2) DEFAULT NULL,
  action_taken ENUM('none','scale_up','scale_down','pause','kill','resume') NOT NULL DEFAULT 'none',
  action_reason VARCHAR(255) DEFAULT NULL,
  UNIQUE KEY uk_account_date (account_id, spend_date),
  KEY idx_date (spend_date, roas)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- nn_server_costs — node cost tracking for true P&L
CREATE TABLE IF NOT EXISTS nn_server_costs (
  id                 INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  node_id            INT UNSIGNED NOT NULL,
  cost_date          DATE NOT NULL,
  hosting_cost       DECIMAL(10,2) NOT NULL DEFAULT 0,
  bandwidth_cost     DECIMAL(10,2) NOT NULL DEFAULT 0,
  storage_cost       DECIMAL(10,2) NOT NULL DEFAULT 0,
  gpu_cost           DECIMAL(10,2) NOT NULL DEFAULT 0,
  payment_source_id  INT UNSIGNED DEFAULT NULL,
  total_cost         DECIMAL(10,2) NOT NULL DEFAULT 0,
  attributed_revenue DECIMAL(12,2) NOT NULL DEFAULT 0,
  net_profit         DECIMAL(12,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uk_node_date (node_id, cost_date),
  KEY idx_profit (net_profit)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- nn_ad_automation_rules — user-defined scaling rules
CREATE TABLE IF NOT EXISTS nn_ad_automation_rules (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id       INT UNSIGNED NOT NULL,
  rule_name        VARCHAR(100) NOT NULL,
  trigger_metric   ENUM('roas','cpa','ctr','spend','conversions','revenue') NOT NULL DEFAULT 'roas',
  trigger_operator ENUM('gt','lt','gte','lte','eq') NOT NULL DEFAULT 'gt',
  trigger_value    DECIMAL(10,2) NOT NULL DEFAULT 0,
  trigger_window   ENUM('today','7day','30day','lifetime') NOT NULL DEFAULT 'today',
  action_type      ENUM('increase_budget','decrease_budget','pause','resume','kill','notify','create_lookalike') NOT NULL DEFAULT 'increase_budget',
  action_value     DECIMAL(10,2) DEFAULT NULL,
  action_is_percent TINYINT(1) NOT NULL DEFAULT 1,
  max_daily_budget DECIMAL(12,2) NOT NULL DEFAULT 1000.00,
  min_daily_budget DECIMAL(12,2) NOT NULL DEFAULT 10.00,
  max_monthly_spend DECIMAL(12,2) NOT NULL DEFAULT 30000.00,
  is_active        TINYINT(1) NOT NULL DEFAULT 1,
  last_triggered_at DATETIME DEFAULT NULL,
  trigger_count    INT UNSIGNED NOT NULL DEFAULT 0,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_account (account_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Views
CREATE OR REPLACE VIEW campaign_performance AS
SELECT
  c.id AS campaign_id, c.name AS campaign_name, c.status AS campaign_status,
  p.platform_name, a.name AS account_name,
  c.budget_amount AS daily_budget, c.spend AS total_spend,
  c.revenue_attributed AS total_revenue, c.roas, c.cpa, c.ctr, c.clicks, c.conversions,
  CASE
    WHEN c.roas >= 3.0 THEN 'rocket'
    WHEN c.roas >= 2.0 THEN 'profitable'
    WHEN c.roas >= 1.0 THEN 'break_even'
    WHEN c.roas > 0    THEN 'losing'
    ELSE 'no_data'
  END AS health_status
FROM nn_mkt_campaigns c
LEFT JOIN nn_ad_platforms p ON p.id = c.platform_id
LEFT JOIN nn_mkt_ad_accounts a ON a.id = c.account_id;

CREATE OR REPLACE VIEW self_funding_status AS
SELECT
  DATE_FORMAT(NOW(), '%Y-%m-%d') AS today,
  (SELECT SUM(total_cost) FROM nn_server_costs WHERE cost_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS server_costs_30d,
  (SELECT SUM(spend_amount) FROM nn_ad_spend_daily WHERE spend_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS ad_spend_30d,
  (SELECT SUM(attributed_revenue) FROM nn_revenue_attribution WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) AS revenue_30d,
  (SELECT COUNT(*) FROM nn_mkt_campaigns WHERE status = 'active') AS active_campaigns,
  (SELECT COUNT(*) FROM nn_mkt_ad_accounts WHERE auto_scale_enabled = 1 AND status = 'active') AS auto_scaling_accounts,
  (SELECT SUM(daily_budget) FROM nn_mkt_ad_accounts WHERE status = 'active') AS total_daily_budget;
