CREATE TABLE IF NOT EXISTS tenant_integrations (
    uuid        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(uuid),
    kind        VARCHAR(40) NOT NULL CHECK (kind IN ('email', 'slack', 'teams')),
    config      JSONB NOT NULL DEFAULT '{}',
    created_at  TIMESTAMP DEFAULT NOW(),
    updated_at  TIMESTAMP DEFAULT NOW(),
    UNIQUE (tenant_id, kind)
);

CREATE TABLE IF NOT EXISTS stay_surveys (
    uuid        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(uuid),
    title       VARCHAR(255) NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT', 'OPEN', 'CLOSED')),
    questions   JSONB NOT NULL DEFAULT '[]',
    created_by  UUID REFERENCES users(uuid),
    created_at  TIMESTAMP DEFAULT NOW(),
    updated_at  TIMESTAMP DEFAULT NOW(),
    closes_at   TIMESTAMP
);

CREATE TABLE IF NOT EXISTS stay_survey_tokens (
    uuid        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(uuid),
    survey_uuid UUID NOT NULL REFERENCES stay_surveys(uuid) ON DELETE CASCADE,
    token       VARCHAR(160) NOT NULL UNIQUE,
    created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS stay_survey_responses (
    uuid        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(uuid),
    survey_uuid UUID NOT NULL REFERENCES stay_surveys(uuid) ON DELETE CASCADE,
    token_uuid  UUID REFERENCES stay_survey_tokens(uuid) ON DELETE SET NULL,
    answers     JSONB NOT NULL DEFAULT '[]',
    created_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS manager_insights (
    uuid         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    UUID NOT NULL REFERENCES tenants(uuid),
    category     VARCHAR(40) NOT NULL CHECK (category IN ('engagement', 'retention', 'wellbeing', 'performance')),
    content      TEXT NOT NULL,
    fri_score    DECIMAL(5,2) NOT NULL DEFAULT 0,
    fri_delta    DECIMAL(6,2),
    generated_by UUID REFERENCES users(uuid),
    created_at   TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS offboarding_cases (
    uuid                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id             UUID NOT NULL REFERENCES tenants(uuid),
    employee_label        VARCHAR(255) NOT NULL,
    employee_mask         VARCHAR(255) NOT NULL,
    departure_date        DATE,
    status                VARCHAR(40) NOT NULL DEFAULT 'INTERVIEW_DONE' CHECK (status IN ('INITIATED', 'IN_PROGRESS', 'INTERVIEW_DONE', 'COMPLETED')),
    root_causes           TEXT[] NOT NULL DEFAULT '{}',
    retention_cost        DECIMAL(12,2),
    boomerang_score       DECIMAL(5,4),
    transcript            JSONB NOT NULL DEFAULT '[]',
    created_by            UUID REFERENCES users(uuid),
    created_at            TIMESTAMP DEFAULT NOW(),
    updated_at            TIMESTAMP DEFAULT NOW(),
    completed_at          TIMESTAMP
);

CREATE INDEX IF NOT EXISTS stay_surveys_tenant_status_idx ON stay_surveys (tenant_id, status, created_at DESC);
CREATE INDEX IF NOT EXISTS stay_survey_tokens_survey_idx ON stay_survey_tokens (tenant_id, survey_uuid);
CREATE INDEX IF NOT EXISTS stay_survey_responses_survey_idx ON stay_survey_responses (tenant_id, survey_uuid, created_at DESC);
CREATE INDEX IF NOT EXISTS manager_insights_tenant_created_idx ON manager_insights (tenant_id, created_at DESC);
CREATE INDEX IF NOT EXISTS offboarding_cases_tenant_created_idx ON offboarding_cases (tenant_id, created_at DESC);

DO $$
DECLARE
    table_name TEXT;
BEGIN
    FOREACH table_name IN ARRAY ARRAY[
        'tenant_integrations',
        'stay_surveys',
        'stay_survey_tokens',
        'stay_survey_responses',
        'manager_insights',
        'offboarding_cases'
    ]
    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 ON tenant_integrations TO diwall_core_app;
GRANT SELECT, INSERT, UPDATE ON stay_surveys TO diwall_core_app;
GRANT SELECT, INSERT ON stay_survey_tokens TO diwall_core_app;
GRANT SELECT, INSERT ON stay_survey_responses TO diwall_core_app;
GRANT SELECT, INSERT ON manager_insights TO diwall_core_app;
GRANT SELECT, INSERT, UPDATE ON offboarding_cases TO diwall_core_app;

DROP POLICY IF EXISTS public_open_survey_read ON stay_surveys;
CREATE POLICY public_open_survey_read ON stay_surveys
    FOR SELECT USING (status = 'OPEN');

DROP POLICY IF EXISTS public_survey_token_read ON stay_survey_tokens;
CREATE POLICY public_survey_token_read ON stay_survey_tokens
    FOR SELECT USING (true);

DROP POLICY IF EXISTS public_survey_response_insert ON stay_survey_responses;
CREATE POLICY public_survey_response_insert ON stay_survey_responses
    FOR INSERT WITH CHECK (true);
