-- Revenue Cockpit: projects, goals, tracking, agent task links

CREATE TABLE IF NOT EXISTS revenue_cockpit_goals (
    id SERIAL PRIMARY KEY,
    vision_text TEXT NOT NULL DEFAULT '',
    horizon_text TEXT DEFAULT '',
    tagline TEXT DEFAULT '',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS revenue_projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(32) DEFAULT 'deal',
    margin_month NUMERIC(14, 2),
    margin_year NUMERIC(14, 2),
    target_revenue NUMERIC(14, 2),
    next_steps TEXT,
    status VARCHAR(32) DEFAULT 'active',
    priority VARCHAR(16) DEFAULT 'normal',
    sort_order INTEGER DEFAULT 0,
    cockpit_project_id INTEGER REFERENCES cockpit_projects(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_revenue_projects_status ON revenue_projects(status, sort_order);
CREATE INDEX IF NOT EXISTS idx_revenue_projects_category ON revenue_projects(category);

CREATE TABLE IF NOT EXISTS revenue_objectives (
    id SERIAL PRIMARY KEY,
    project_id INTEGER NOT NULL REFERENCES revenue_projects(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    due_date DATE,
    status VARCHAR(32) DEFAULT 'open',
    priority VARCHAR(16) DEFAULT 'normal',
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_revenue_objectives_project ON revenue_objectives(project_id, status);

CREATE TABLE IF NOT EXISTS revenue_import_runs (
    id SERIAL PRIMARY KEY,
    source_file VARCHAR(512) NOT NULL,
    sheet_name VARCHAR(128) NOT NULL,
    rows_imported INTEGER DEFAULT 0,
    goals_imported BOOLEAN DEFAULT FALSE,
    imported_by VARCHAR(64) DEFAULT 'ceo',
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS revenue_change_log (
    id SERIAL PRIMARY KEY,
    entity_type VARCHAR(32) NOT NULL,
    entity_id INTEGER NOT NULL,
    field_name VARCHAR(64) NOT NULL,
    old_value TEXT,
    new_value TEXT,
    changed_by VARCHAR(64) DEFAULT 'ceo',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_revenue_change_log_entity ON revenue_change_log(entity_type, entity_id, created_at DESC);

CREATE TABLE IF NOT EXISTS revenue_snapshots (
    id SERIAL PRIMARY KEY,
    snapshot_date DATE NOT NULL DEFAULT CURRENT_DATE,
    total_margin_year NUMERIC(14, 2) DEFAULT 0,
    total_margin_month NUMERIC(14, 2) DEFAULT 0,
    active_projects INTEGER DEFAULT 0,
    open_objectives INTEGER DEFAULT 0,
    open_agent_tasks INTEGER DEFAULT 0,
    payload JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (snapshot_date)
);

ALTER TABLE agent_tasks ADD COLUMN IF NOT EXISTS revenue_project_id INTEGER REFERENCES revenue_projects(id) ON DELETE SET NULL;
ALTER TABLE agent_tasks ADD COLUMN IF NOT EXISTS revenue_objective_id INTEGER REFERENCES revenue_objectives(id) ON DELETE SET NULL;
ALTER TABLE agent_tasks ADD COLUMN IF NOT EXISTS source VARCHAR(32) DEFAULT 'manual';

CREATE INDEX IF NOT EXISTS idx_agent_tasks_revenue ON agent_tasks(revenue_project_id, status);