SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS financial_goals (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 scope_type ENUM('product','campaign') NOT NULL,
 scope_id BIGINT UNSIGNED NOT NULL,
 month CHAR(7) NOT NULL,
 budget_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
 target_revenue DECIMAL(14,2) NOT NULL DEFAULT 0,
 target_profit DECIMAL(14,2) NOT NULL DEFAULT 0,
 target_roas DECIMAL(10,4) NOT NULL DEFAULT 0,
 notes TEXT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_fg_scope_month(workspace_id,scope_type,scope_id,month),
 INDEX idx_fg_month(workspace_id,month),
 CONSTRAINT fk_fg_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_fg_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
