SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS marketing_costs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 workspace_id BIGINT UNSIGNED NOT NULL,
 scope_type ENUM('campaign','product') NOT NULL,
 scope_id BIGINT UNSIGNED NOT NULL,
 cost_type VARCHAR(80) NOT NULL DEFAULT 'media',
 amount DECIMAL(14,2) NOT NULL DEFAULT 0,
 cost_date DATE NOT NULL,
 notes TEXT NULL,
 created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_mc_scope(workspace_id,scope_type,scope_id,cost_date),
 INDEX idx_mc_date(workspace_id,cost_date),
 CONSTRAINT fk_mc_workspace FOREIGN KEY(workspace_id) REFERENCES workspaces(id) ON DELETE CASCADE,
 CONSTRAINT fk_mc_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
