-- Budget recommendations generated by the recommendation engine
CREATE TABLE IF NOT EXISTS budget_recommendations (
    id BIGSERIAL PRIMARY KEY,
    target_type VARCHAR(16) NOT NULL,                    -- 'team' or 'key'
    target_id BIGINT NOT NULL,                           -- FK to teams.id or api_keys.id
    period VARCHAR(16) NOT NULL DEFAULT 'monthly',       -- budget period
    recommended_budget DECIMAL(12,2) NOT NULL,           -- AI recommended budget
    current_budget DECIMAL(12,2) NOT NULL DEFAULT 0,     -- current budget (0 = unlimited)
    avg_period_spend DECIMAL(12,2) NOT NULL DEFAULT 0,   -- average spend per period (last 3 periods)
    growth_rate DECIMAL(8,4) NOT NULL DEFAULT 0,         -- period-over-period growth rate
    confidence DECIMAL(5,2) NOT NULL DEFAULT 0,          -- confidence score 0-100
    reasoning TEXT NOT NULL DEFAULT '',                   -- human-readable explanation
    currency VARCHAR(3) NOT NULL DEFAULT 'CNY',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_budget_recs_target ON budget_recommendations(target_type, target_id);
CREATE INDEX idx_budget_recs_created ON budget_recommendations(created_at DESC);

-- Budget change requests with approval workflow
CREATE TABLE IF NOT EXISTS budget_requests (
    id BIGSERIAL PRIMARY KEY,
    target_type VARCHAR(16) NOT NULL,                    -- 'team' or 'key'
    target_id BIGINT NOT NULL,                           -- FK to teams.id or api_keys.id
    period VARCHAR(16) NOT NULL DEFAULT 'monthly',
    current_budget DECIMAL(12,2) NOT NULL DEFAULT 0,
    requested_budget DECIMAL(12,2) NOT NULL,             -- requested new budget
    reason TEXT NOT NULL DEFAULT '',                      -- applicant's reason
    recommendation_id BIGINT,                             -- optional FK to budget_recommendations
    status VARCHAR(16) NOT NULL DEFAULT 'pending',       -- pending, approved, rejected, cancelled
    created_by BIGINT NOT NULL,                          -- user who created the request
    reviewed_by BIGINT,                                   -- user who approved/rejected
    review_comment TEXT NOT NULL DEFAULT '',              -- reviewer's comment
    reviewed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_budget_reqs_target ON budget_requests(target_type, target_id);
CREATE INDEX idx_budget_reqs_status ON budget_requests(status);
CREATE INDEX idx_budget_reqs_creator ON budget_requests(created_by);
CREATE INDEX idx_budget_reqs_reviewer ON budget_requests(reviewed_by);
CREATE INDEX idx_budget_reqs_created ON budget_requests(created_at DESC);
