-- Metadata auth management schema patch.
-- Keycloak owns login credentials; TJWater stores only business identity and access.

CREATE EXTENSION IF NOT EXISTS pgcrypto;

DO $$
DECLARE
    users_id_type text;
BEGIN
    SELECT data_type INTO users_id_type
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'users'
      AND column_name = 'id';

    IF users_id_type IS NULL THEN
        CREATE TABLE users (
            id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
            keycloak_id UUID UNIQUE NOT NULL,
            username VARCHAR(50) UNIQUE NOT NULL,
            email VARCHAR(100) UNIQUE NOT NULL,
            role VARCHAR(20) DEFAULT 'user' NOT NULL,
            is_active BOOLEAN DEFAULT TRUE NOT NULL,
            is_superuser BOOLEAN DEFAULT FALSE NOT NULL,
            attributes JSONB,
            created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
            updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
            last_login_at TIMESTAMP WITH TIME ZONE
        );
    ELSIF users_id_type <> 'uuid' THEN
        RAISE EXCEPTION
            'Existing public.users.id is %, not uuid. Export old local users, create Keycloak accounts, then migrate to metadata UUID users before applying this patch.',
            users_id_type;
    END IF;
END $$;

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS keycloak_id UUID,
    ADD COLUMN IF NOT EXISTS attributes JSONB,
    ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMP WITH TIME ZONE;

ALTER TABLE users
    ALTER COLUMN role SET DEFAULT 'user';

CREATE UNIQUE INDEX IF NOT EXISTS idx_users_keycloak_id ON users(keycloak_id);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_users_is_active ON users(is_active);

CREATE TABLE IF NOT EXISTS user_project_membership (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    project_id UUID NOT NULL,
    project_role VARCHAR(20) DEFAULT 'viewer' NOT NULL,
    CONSTRAINT user_project_membership_role_check
        CHECK (project_role IN ('owner', 'admin', 'member', 'viewer')),
    CONSTRAINT user_project_membership_unique UNIQUE (user_id, project_id)
);

CREATE INDEX IF NOT EXISTS idx_user_project_membership_user_id
    ON user_project_membership(user_id);
CREATE INDEX IF NOT EXISTS idx_user_project_membership_project_id
    ON user_project_membership(project_id);