CREATE SCHEMA core;

-- Tenant table (Enterprise / Team)
CREATE TABLE core.tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    plan TEXT DEFAULT 'free',
    config JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Agent Registry
CREATE TABLE core.agents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core.tenants(id),
    name TEXT NOT NULL,
    role TEXT NOT NULL,          -- planner / researcher / coder / reviewer
    capabilities TEXT[],         -- Capability Tags
    config JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Project table
CREATE TABLE core.projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES core.tenants(id),
    name TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Project members (Agents participating in a project)
CREATE TABLE core.project_members (
    project_id UUID REFERENCES core.projects(id),
    agent_id UUID REFERENCES core.agents(id),
    role TEXT DEFAULT 'member',  -- owner / member / readonly
    joined_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (project_id, agent_id)
);

-- Project-level shared memory
CREATE TABLE memory.shared_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    project_id UUID NOT NULL,
    author_agent_id UUID NOT NULL,   -- Memory creator
    content TEXT NOT NULL,
    embedding vector(1024),
    record_type TEXT DEFAULT 'finding',  -- finding / decision / context / artifact
    visibility TEXT DEFAULT 'project',   -- project / team / private
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- RLS: Agents within the same project can access each other's memory.
ALTER TABLE memory.shared_records ENABLE ROW LEVEL SECURITY;

CREATE POLICY project_access ON memory.shared_records
    USING (
        project_id IN (
            SELECT project_id FROM core.project_members
            WHERE agent_id = current_setting('app.agent_id')::uuid
        )
    );

-- Researcher Agent writes research conclusions.
SET app.agent_id = 'researcher-001';

INSERT INTO memory.shared_records
    (tenant_id, project_id, author_agent_id, content, embedding, record_type)
VALUES (
    :tenant_id,
    :project_id,
    'researcher-001',
    The vector search performance of the competing product PolarDB achieves a QPS of approximately 2000 on datasets at the million-row scale.
    tencentdb_ai.get_embedding('bge-m3', 'Vector search performance of the competing product PolarDB'),
    'finding'
);

-- Planner Agent searches for Researcher's findings.
SET app.agent_id = 'planner-001';

SELECT content, author_agent_id, created_at
FROM memory.shared_records
WHERE project_id = :project_id
ORDER BY embedding <=> tencentdb_ai.get_embedding('bge-m3', 'Competing product performance data')
LIMIT 5;

-- Task table
CREATE TABLE core.tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL,
    assigner_id UUID NOT NULL,   -- Assigner
    assignee_id UUID NOT NULL,   -- Assignee
    title TEXT NOT NULL,
    description TEXT,
    status TEXT DEFAULT 'pending',  -- pending / in_progress / completed / failed
    result TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ
);

-- Planner assigns tasks to Researcher.
INSERT INTO core.tasks (project_id, assigner_id, assignee_id, title, description)
VALUES (:project_id, 'planner-001', 'researcher-001',
        'Investigate DiskANN index performance', 'Compare the performance of HNSW and DiskANN on 10 million vectors');

-- Update after Researcher completes.
UPDATE core.tasks
SET status = 'completed',
    result = 'DiskANN achieves a 40% QPS improvement and a 60% memory reduction on 10 million vectors.',
    completed_at = NOW()
WHERE id = :task_id;

-- Record team decisions.
INSERT INTO memory.shared_records
    (tenant_id, project_id, author_agent_id, content, record_type, metadata)
VALUES (
    :tenant_id, :project_id, 'planner-001',
    'Decided to adopt pgvectorscale DiskANN as the vector index solution.',
    'decision',
    '{"reason": "Better performance and lower memory usage", "alternatives": ["HNSW", "IVFFlat"]}'
);

-- readonly members can only be read, not written.
CREATE POLICY readonly_member ON memory.shared_records
    FOR INSERT
    WITH CHECK (
        EXISTS (
            SELECT 1 FROM core.project_members
            WHERE project_id = memory.shared_records.project_id
              AND agent_id = current_setting('app.agent_id')::uuid
              AND role IN ('owner', 'member')
        )
    );