-- IAM users table alignment with Cutehorse Users module
-- PostgreSQL

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS iam;

CREATE TABLE IF NOT EXISTS iam.users (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id uuid NULL,
    branch_id uuid NULL,
    employee_id uuid NULL,
    first_name varchar(100) NULL,
    last_name varchar(100) NULL,
    other_names varchar(100) NULL,
    email varchar(100) NULL,
    phone varchar(50) NULL,
    username varchar(100) NULL,
    password_hash text NULL,
    must_change_password boolean NULL,
    last_login_at timestamp without time zone NULL,
    status varchar(30) NULL,
    is_active boolean DEFAULT TRUE,
    is_locked boolean DEFAULT FALSE,
    failed_login_attempts integer DEFAULT 0,
    avatar_url text NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL
);

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'tenant_id'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN tenant_id uuid NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'branch_id'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN branch_id uuid NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'employee_id'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN employee_id uuid NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'first_name'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN first_name varchar(100) NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'last_name'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN last_name varchar(100) NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'other_names'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN other_names varchar(100) NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'email'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN email varchar(100) NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'phone'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN phone varchar(50) NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'username'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN username varchar(100) NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'password_hash'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN password_hash text NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'must_change_password'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN must_change_password boolean NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'last_login_at'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN last_login_at timestamp without time zone NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'status'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN status varchar(30) NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'is_active'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN is_active boolean DEFAULT TRUE;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'is_locked'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN is_locked boolean DEFAULT FALSE;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'failed_login_attempts'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN failed_login_attempts integer DEFAULT 0;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'avatar_url'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN avatar_url text NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'created_at'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'updated_at'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'created_by'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN created_by uuid NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'updated_by'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN updated_by uuid NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'deleted_at'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN deleted_at timestamp without time zone NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_schema = 'iam' AND table_name = 'users' AND column_name = 'deleted_by'
    ) THEN
        ALTER TABLE iam.users ADD COLUMN deleted_by uuid NULL;
    END IF;
END $$;

ALTER TABLE iam.users ALTER COLUMN id SET DEFAULT gen_random_uuid();
ALTER TABLE iam.users ALTER COLUMN is_active SET DEFAULT TRUE;
ALTER TABLE iam.users ALTER COLUMN is_locked SET DEFAULT FALSE;
ALTER TABLE iam.users ALTER COLUMN failed_login_attempts SET DEFAULT 0;
ALTER TABLE iam.users ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE iam.users ALTER COLUMN updated_at SET DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE iam.users ALTER COLUMN deleted_at DROP DEFAULT;

CREATE INDEX IF NOT EXISTS idx_users_tenant_active ON iam.users (tenant_id, is_active) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_users_tenant_email ON iam.users (tenant_id, lower(email)) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_users_tenant_username ON iam.users (tenant_id, lower(username)) WHERE deleted_at IS NULL;