-- dd0c/portal V1 schema — PostgreSQL with RLS + Meilisearch for search

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Tenants
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    tier TEXT NOT NULL DEFAULT 'free' CHECK (tier IN ('free', 'pro')),
    service_count INT NOT NULL DEFAULT 0,
    max_services INT NOT NULL DEFAULT 50,  -- Free tier: 50
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Service catalog
CREATE TABLE services (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    type TEXT NOT NULL DEFAULT 'unknown',
    owner TEXT NOT NULL DEFAULT 'unknown',
    owner_source TEXT NOT NULL DEFAULT 'heuristic' CHECK (owner_source IN ('config', 'codeowners', 'aws-tag', 'heuristic')),
    description TEXT,
    tier TEXT DEFAULT 'medium' CHECK (tier IN ('critical', 'high', 'medium', 'low')),
    lifecycle TEXT DEFAULT 'active' CHECK (lifecycle IN ('active', 'deprecated', 'decommissioned')),
    links JSONB NOT NULL DEFAULT '{}',
    tags JSONB NOT NULL DEFAULT '{}',
    metadata JSONB NOT NULL DEFAULT '{}',
    last_discovered_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE(tenant_id, name)
);
CREATE INDEX idx_services_tenant ON services(tenant_id);
CREATE INDEX idx_services_owner ON services(tenant_id, owner);
CREATE INDEX idx_services_type ON services(tenant_id, type);

-- Staged updates (partial scan results awaiting review)
CREATE TABLE staged_updates (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    service_name TEXT NOT NULL,
    source TEXT NOT NULL CHECK (source IN ('aws', 'github', 'manual')),
    changes JSONB NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'applied', 'rejected')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_staged_tenant ON staged_updates(tenant_id, status);

-- Discovery scan history
CREATE TABLE scan_history (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    scanner TEXT NOT NULL CHECK (scanner IN ('aws', 'github')),
    status TEXT NOT NULL CHECK (status IN ('success', 'partial_failure', 'failed')),
    discovered INT NOT NULL DEFAULT 0,
    errors TEXT[] NOT NULL DEFAULT '{}',
    started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ
);

-- RLS
ALTER TABLE services ENABLE ROW LEVEL SECURITY;
ALTER TABLE staged_updates ENABLE ROW LEVEL SECURITY;
ALTER TABLE scan_history ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_iso_services ON services
    USING (tenant_id::text = current_setting('app.tenant_id', true));
CREATE POLICY tenant_iso_staged ON staged_updates
    USING (tenant_id::text = current_setting('app.tenant_id', true));
CREATE POLICY tenant_iso_scans ON scan_history
    USING (tenant_id::text = current_setting('app.tenant_id', true));