CREATE TABLE IF NOT EXISTS coach_conversations (
    uuid            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(uuid),
    employee_uuid   UUID NOT NULL REFERENCES employees(uuid),
    messages        JSONB NOT NULL,
    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS coach_usage_events (
    uuid            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(uuid),
    employee_uuid   UUID NOT NULL REFERENCES employees(uuid),
    created_at      TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS coach_conversations_employee_updated_idx ON coach_conversations (tenant_id, employee_uuid, updated_at DESC);
CREATE INDEX IF NOT EXISTS coach_usage_employee_month_idx ON coach_usage_events (tenant_id, employee_uuid, created_at DESC);

DO $$
DECLARE
    table_name TEXT;
BEGIN
    FOREACH table_name IN ARRAY ARRAY[
        'coach_conversations',
        'coach_usage_events'
    ]
    LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', table_name);
        EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', table_name);
        EXECUTE format('DROP POLICY IF EXISTS tenant_isolation ON %I', table_name);
        EXECUTE format(
            'CREATE POLICY tenant_isolation ON %I USING (tenant_id = current_setting(''app.tenant_id'')::uuid)',
            table_name
        );
    END LOOP;
END $$;

GRANT SELECT, INSERT, UPDATE, DELETE ON coach_conversations TO diwall_core_app;
GRANT SELECT, INSERT ON coach_usage_events TO diwall_core_app;
