CREATE TABLE IF NOT EXISTS employees (
    uuid        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(uuid),
    user_uuid   UUID REFERENCES users(uuid),
    department  VARCHAR(100),
    role_title  VARCHAR(100),
    start_date  DATE,
    end_date    DATE,
    status      VARCHAR(50) DEFAULT 'active',
    created_at  TIMESTAMP DEFAULT NOW(),
    updated_at  TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS disc_profiles (
    uuid            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(uuid),
    employee_uuid   UUID NOT NULL REFERENCES employees(uuid),
    type            VARCHAR(20) NOT NULL CHECK (type IN ('natural', 'adapted')),
    d_score         DECIMAL(5,2) NOT NULL CHECK (d_score >= 0 AND d_score <= 100),
    i_score         DECIMAL(5,2) NOT NULL CHECK (i_score >= 0 AND i_score <= 100),
    s_score         DECIMAL(5,2) NOT NULL CHECK (s_score >= 0 AND s_score <= 100),
    c_score         DECIMAL(5,2) NOT NULL CHECK (c_score >= 0 AND c_score <= 100),
    dominant        VARCHAR(1) NOT NULL CHECK (dominant IN ('D', 'I', 'S', 'C')),
    secondary       VARCHAR(1) CHECK (secondary IN ('D', 'I', 'S', 'C')),
    phase           VARCHAR(50) NOT NULL CHECK (phase IN ('onboarding', 'checkin', 'offboarding')),
    raw_responses   JSONB NOT NULL,
    created_at      TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS checkins (
    uuid            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(uuid),
    employee_uuid   UUID NOT NULL REFERENCES employees(uuid),
    phase           VARCHAR(50) NOT NULL,
    responses       JSONB NOT NULL,
    drift_score     DECIMAL(5,2),
    fri_delta       DECIMAL(5,2),
    created_at      TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS employees_tenant_user_idx ON employees (tenant_id, user_uuid);
CREATE INDEX IF NOT EXISTS disc_profiles_employee_type_created_idx ON disc_profiles (tenant_id, employee_uuid, type, created_at DESC);
CREATE INDEX IF NOT EXISTS checkins_employee_created_idx ON checkins (tenant_id, employee_uuid, created_at DESC);

DO $$
DECLARE
    table_name TEXT;
BEGIN
    FOREACH table_name IN ARRAY ARRAY[
        'employees',
        'disc_profiles',
        'checkins'
    ]
    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 $$;
