SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS investment_decisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  workspace_id BIGINT UNSIGNED NOT NULL,
  objective VARCHAR(40) NOT NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'approved',
  total_budget DECIMAL(14,2) NOT NULL DEFAULT 0,
  period_days INT NOT NULL DEFAULT 30,
  decision_score INT NOT NULL DEFAULT 0,
  fit_label VARCHAR(80) NULL,
  allocation_snapshot JSON NOT NULL,
  expected_snapshot JSON NOT NULL,
  risk_snapshot JSON NOT NULL,
  rationale TEXT NULL,
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  decided_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_investment_decisions_workspace_status (workspace_id,status),
  KEY idx_investment_decisions_decided_at (workspace_id,decided_at),
  CONSTRAINT fk_investment_decisions_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_investment_decisions_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS investment_decision_results (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  workspace_id BIGINT UNSIGNED NOT NULL,
  decision_id BIGINT UNSIGNED NOT NULL,
  actual_revenue DECIMAL(14,2) NOT NULL DEFAULT 0,
  actual_cost DECIMAL(14,2) NOT NULL DEFAULT 0,
  actual_profit DECIMAL(14,2) NOT NULL DEFAULT 0,
  actual_roas DECIMAL(12,4) NULL,
  actual_roi DECIMAL(12,4) NULL,
  actual_conversions INT NOT NULL DEFAULT 0,
  notes TEXT NULL,
  measured_at DATETIME NOT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_investment_decision_results_workspace_decision (workspace_id,decision_id),
  KEY idx_investment_decision_results_measured (workspace_id,measured_at),
  CONSTRAINT fk_investment_decision_results_workspace FOREIGN KEY (workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
  CONSTRAINT fk_investment_decision_results_decision FOREIGN KEY (decision_id) REFERENCES investment_decisions(id) ON DELETE CASCADE,
  CONSTRAINT fk_investment_decision_results_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
