SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS performance_goals (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  metric ENUM('revenue','conversions','cpa','roi','roas') NOT NULL,
  operator ENUM('gte','lte') NOT NULL DEFAULT 'gte',
  target_value DECIMAL(16,4) NOT NULL,
  period_days INT UNSIGNED NOT NULL DEFAULT 30,
  status ENUM('active','archived') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_perf_goal(workspace_id,campaign_id,metric),
  INDEX idx_perf_goal_ws_status(workspace_id,status),
  CONSTRAINT fk_pg_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pg_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS performance_goal_evaluations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  goal_id BIGINT UNSIGNED NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  current_value DECIMAL(16,4) NULL,
  status ENUM('met','missed','unavailable') NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pge_goal_time(goal_id,created_at),
  CONSTRAINT fk_pge_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pge_goal FOREIGN KEY(goal_id) REFERENCES performance_goals(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS performance_alerts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  workspace_id BIGINT UNSIGNED NOT NULL,
  goal_id BIGINT UNSIGNED NOT NULL,
  campaign_id BIGINT UNSIGNED NOT NULL,
  metric ENUM('revenue','conversions','cpa','roi','roas') NOT NULL,
  current_value DECIMAL(16,4) NULL,
  target_value DECIMAL(16,4) NOT NULL,
  period_from DATE NOT NULL,
  period_to DATE NOT NULL,
  status ENUM('open','resolved') NOT NULL DEFAULT 'open',
  first_triggered_at DATETIME NOT NULL,
  last_triggered_at DATETIME NOT NULL,
  resolved_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_perf_alert_open(goal_id,status),
  INDEX idx_perf_alert_ws_status(workspace_id,status),
  CONSTRAINT fk_pa_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_pa_goal FOREIGN KEY(goal_id) REFERENCES performance_goals(id) ON DELETE CASCADE,
  CONSTRAINT fk_pa_campaign FOREIGN KEY(campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
