MH-DashBoard-organization/backend/app/db.py

from contextlib import contextmanager
import time
from typing import Iterator

from psycopg.rows import dict_row
import psycopg

from .config import DATABASE_URL


SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS members (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    employee_id TEXT,
    company TEXT,
    rank TEXT,
    role TEXT,
    department TEXT,
    grp TEXT,
    division TEXT,
    team TEXT,
    cell TEXT,
    work_status TEXT,
    work_time TEXT,
    phone TEXT,
    email TEXT,
    seat_label TEXT,
    photo_url TEXT,
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS member_overrides (
    id SERIAL PRIMARY KEY,
    employee_id TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL DEFAULT '',
    company TEXT,
    rank TEXT,
    role TEXT,
    department TEXT,
    grp TEXT,
    division TEXT,
    team TEXT,
    cell TEXT,
    work_status TEXT,
    work_time TEXT,
    phone TEXT,
    email TEXT,
    seat_label TEXT,
    photo_url TEXT,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS member_retirements (
    id SERIAL PRIMARY KEY,
    employee_id TEXT,
    name TEXT NOT NULL,
    note TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS member_aliases (
    id SERIAL PRIMARY KEY,
    alias_name TEXT NOT NULL UNIQUE,
    canonical_name TEXT NOT NULL,
    employee_id TEXT,
    note TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS seat_maps (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    image_url TEXT NOT NULL,
    source_type TEXT NOT NULL DEFAULT 'image',
    source_url TEXT,
    preview_svg TEXT,
    view_box_min_x DOUBLE PRECISION,
    view_box_min_y DOUBLE PRECISION,
    view_box_width DOUBLE PRECISION,
    view_box_height DOUBLE PRECISION,
    image_width INTEGER,
    image_height INTEGER,
    grid_rows INTEGER NOT NULL,
    grid_cols INTEGER NOT NULL,
    cell_gap INTEGER NOT NULL DEFAULT 0,
    is_active BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS seat_positions (
    member_id INTEGER PRIMARY KEY REFERENCES members(id) ON DELETE CASCADE,
    seat_map_id INTEGER REFERENCES seat_maps(id) ON DELETE CASCADE,
    seat_slot_id INTEGER,
    row_index INTEGER NOT NULL DEFAULT 0,
    col_index INTEGER NOT NULL DEFAULT 0,
    seat_label TEXT,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS seat_slots (
    id SERIAL PRIMARY KEY,
    seat_map_id INTEGER NOT NULL REFERENCES seat_maps(id) ON DELETE CASCADE,
    slot_key TEXT NOT NULL,
    label TEXT NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    rotation DOUBLE PRECISION NOT NULL DEFAULT 0,
    layer_name TEXT NOT NULL DEFAULT 'chair',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (seat_map_id, slot_key)
);

CREATE TABLE IF NOT EXISTS integration_import_batches (
    id SERIAL PRIMARY KEY,
    source_key TEXT NOT NULL UNIQUE,
    source_name TEXT NOT NULL,
    source_path TEXT NOT NULL,
    imported_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    row_count INTEGER NOT NULL DEFAULT 0,
    meta_json JSONB NOT NULL DEFAULT '{}'::jsonb
);

CREATE TABLE IF NOT EXISTS integration_raw_organization_rows (
    id SERIAL PRIMARY KEY,
    batch_id INTEGER NOT NULL REFERENCES integration_import_batches(id) ON DELETE CASCADE,
    row_index INTEGER NOT NULL,
    row_json JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS integration_raw_mh_rows (
    id SERIAL PRIMARY KEY,
    batch_id INTEGER NOT NULL REFERENCES integration_import_batches(id) ON DELETE CASCADE,
    row_index INTEGER NOT NULL,
    row_json JSONB NOT NULL,
    row_values_json JSONB NOT NULL DEFAULT '[]'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS integration_raw_mh_pm_rows (
    id SERIAL PRIMARY KEY,
    batch_id INTEGER NOT NULL REFERENCES integration_import_batches(id) ON DELETE CASCADE,
    row_index INTEGER NOT NULL,
    row_values_json JSONB NOT NULL DEFAULT '[]'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS integration_raw_payment_rows (
    id SERIAL PRIMARY KEY,
    batch_id INTEGER NOT NULL REFERENCES integration_import_batches(id) ON DELETE CASCADE,
    row_index INTEGER NOT NULL,
    row_json JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS integration_projects (
    id SERIAL PRIMARY KEY,
    project_code TEXT NOT NULL UNIQUE,
    project_name TEXT NOT NULL,
    display_name TEXT NOT NULL DEFAULT '',
    intranet_name TEXT NOT NULL DEFAULT '',
    business_area TEXT NOT NULL DEFAULT '',
    business_subarea TEXT NOT NULL DEFAULT '',
    project_nature TEXT NOT NULL DEFAULT '',
    main_category TEXT NOT NULL DEFAULT '',
    middle_category TEXT NOT NULL DEFAULT '',
    sub_category TEXT NOT NULL DEFAULT '',
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS integration_project_aliases (
    id SERIAL PRIMARY KEY,
    project_id INTEGER NOT NULL REFERENCES integration_projects(id) ON DELETE CASCADE,
    alias_name TEXT NOT NULL,
    alias_type TEXT NOT NULL DEFAULT 'name',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (project_id, alias_name, alias_type)
);

CREATE TABLE IF NOT EXISTS integration_project_category_mappings (
    id SERIAL PRIMARY KEY,
    source_key TEXT NOT NULL DEFAULT 'ptj_csv',
    project_name TEXT NOT NULL,
    normalized_project_key TEXT NOT NULL,
    mapped_d1 TEXT NOT NULL DEFAULT '',
    mapped_d2 TEXT NOT NULL DEFAULT '',
    mapped_d3 TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (source_key, normalized_project_key)
);

CREATE TABLE IF NOT EXISTS integration_project_pm_assignments (
    id SERIAL PRIMARY KEY,
    project_id INTEGER NOT NULL REFERENCES integration_projects(id) ON DELETE CASCADE,
    member_id INTEGER REFERENCES members(id) ON DELETE SET NULL,
    pm_name TEXT NOT NULL,
    source_label TEXT NOT NULL DEFAULT 'mh_sheet2',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (project_id, source_label)
);

CREATE TABLE IF NOT EXISTS integration_work_logs (
    id SERIAL PRIMARY KEY,
    work_date DATE NOT NULL,
    employee_id TEXT NOT NULL,
    member_id INTEGER REFERENCES members(id) ON DELETE SET NULL,
    member_name TEXT NOT NULL,
    title TEXT NOT NULL DEFAULT '',
    team_category TEXT NOT NULL DEFAULT '',
    team_name TEXT NOT NULL DEFAULT '',
    user_state TEXT NOT NULL DEFAULT '',
    shift_hours NUMERIC(10, 2) NOT NULL DEFAULT 0,
    weekend_late_flag TEXT NOT NULL DEFAULT '',
    review_status TEXT NOT NULL DEFAULT '',
    source_row_index INTEGER NOT NULL DEFAULT 0,
    raw_batch_id INTEGER REFERENCES integration_import_batches(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (work_date, employee_id, source_row_index)
);

CREATE TABLE IF NOT EXISTS integration_work_log_segments (
    id SERIAL PRIMARY KEY,
    work_log_id INTEGER NOT NULL REFERENCES integration_work_logs(id) ON DELETE CASCADE,
    slot_name TEXT NOT NULL,
    project_id INTEGER REFERENCES integration_projects(id) ON DELETE SET NULL,
    project_code TEXT NOT NULL DEFAULT '',
    project_name TEXT NOT NULL DEFAULT '',
    business_type TEXT NOT NULL DEFAULT '',
    activity_code TEXT NOT NULL DEFAULT '',
    hours NUMERIC(10, 2) NOT NULL DEFAULT 0,
    overtime_hours_raw NUMERIC(10, 2) NOT NULL DEFAULT 0,
    overtime_hours_adjusted NUMERIC(10, 2) NOT NULL DEFAULT 0,
    is_overtime BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS integration_vouchers (
    id SERIAL PRIMARY KEY,
    accounting_company TEXT NOT NULL DEFAULT '',
    claim_date DATE,
    issue_date DATE,
    issue_month TEXT NOT NULL DEFAULT '',
    account_code TEXT NOT NULL DEFAULT '',
    management_account_code TEXT NOT NULL DEFAULT '',
    account_name TEXT NOT NULL DEFAULT '',
    project_id INTEGER REFERENCES integration_projects(id) ON DELETE SET NULL,
    project_code TEXT NOT NULL DEFAULT '',
    project_name TEXT NOT NULL DEFAULT '',
    display_project_name TEXT NOT NULL DEFAULT '',
    intranet_project_name TEXT NOT NULL DEFAULT '',
    business_area TEXT NOT NULL DEFAULT '',
    business_subarea TEXT NOT NULL DEFAULT '',
    planning_dev_sales TEXT NOT NULL DEFAULT '',
    main_category TEXT NOT NULL DEFAULT '',
    middle_category TEXT NOT NULL DEFAULT '',
    sub_category TEXT NOT NULL DEFAULT '',
    department_name TEXT NOT NULL DEFAULT '',
    team_name TEXT NOT NULL DEFAULT '',
    customer_name TEXT NOT NULL DEFAULT '',
    summary_text TEXT NOT NULL DEFAULT '',
    debit_supply_amount NUMERIC(14, 2) NOT NULL DEFAULT 0,
    credit_supply_amount NUMERIC(14, 2) NOT NULL DEFAULT 0,
    expense_amount NUMERIC(14, 2) NOT NULL DEFAULT 0,
    income_amount NUMERIC(14, 2) NOT NULL DEFAULT 0,
    voucher_type TEXT NOT NULL DEFAULT '',
    project_nature TEXT NOT NULL DEFAULT '',
    raw_batch_id INTEGER REFERENCES integration_import_batches(id) ON DELETE SET NULL,
    source_row_index INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS integration_binary_sources (
    id BIGSERIAL PRIMARY KEY,
    source_key TEXT NOT NULL UNIQUE,
    source_name TEXT NOT NULL,
    filename TEXT NOT NULL DEFAULT '',
    mime_type TEXT NOT NULL DEFAULT 'application/octet-stream',
    content BYTEA NOT NULL,
    content_sha256 TEXT NOT NULL DEFAULT '',
    meta_json JSONB NOT NULL DEFAULT '{}'::jsonb,
    imported_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS history_revisions (
    id BIGSERIAL PRIMARY KEY,
    scope TEXT NOT NULL DEFAULT 'organization',
    revision_label TEXT NOT NULL,
    created_by_user_id BIGINT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    note TEXT NOT NULL DEFAULT ''
);

CREATE TABLE IF NOT EXISTS member_versions (
    id BIGSERIAL PRIMARY KEY,
    member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    company TEXT NOT NULL DEFAULT '',
    rank TEXT NOT NULL DEFAULT '',
    role TEXT NOT NULL DEFAULT '',
    department TEXT NOT NULL DEFAULT '',
    grp TEXT NOT NULL DEFAULT '',
    division TEXT NOT NULL DEFAULT '',
    team TEXT NOT NULL DEFAULT '',
    cell TEXT NOT NULL DEFAULT '',
    work_status TEXT NOT NULL DEFAULT '',
    work_time TEXT NOT NULL DEFAULT '',
    phone TEXT NOT NULL DEFAULT '',
    email TEXT NOT NULL DEFAULT '',
    photo_url TEXT NOT NULL DEFAULT '',
    valid_from TIMESTAMPTZ NOT NULL,
    valid_to TIMESTAMPTZ,
    revision_no BIGINT NOT NULL,
    changed_by_user_id BIGINT,
    change_reason TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS seat_assignment_versions (
    id BIGSERIAL PRIMARY KEY,
    member_id INTEGER NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    seat_map_id INTEGER REFERENCES seat_maps(id) ON DELETE CASCADE,
    seat_slot_id INTEGER REFERENCES seat_slots(id) ON DELETE CASCADE,
    seat_label TEXT NOT NULL DEFAULT '',
    valid_from TIMESTAMPTZ NOT NULL,
    valid_to TIMESTAMPTZ,
    revision_no BIGINT NOT NULL,
    changed_by_user_id BIGINT,
    change_reason TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE SCHEMA IF NOT EXISTS auth;

CREATE TABLE IF NOT EXISTS auth.users (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    display_name TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'admin',
    member_id INTEGER NULL REFERENCES members(id) ON DELETE SET NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_from TEXT NOT NULL DEFAULT 'manual',
    last_login_at TIMESTAMPTZ,
    password_changed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS auth.sessions (
    id UUID PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    expires_at TIMESTAMPTZ NOT NULL,
    revoked_at TIMESTAMPTZ,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS auth.login_audit_logs (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    user_id BIGINT NULL REFERENCES auth.users(id) ON DELETE SET NULL,
    success BOOLEAN NOT NULL,
    failure_reason TEXT,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
"""

MIGRATION_SQL = """
ALTER TABLE members ADD COLUMN IF NOT EXISTS employee_id TEXT;
ALTER TABLE members ADD COLUMN IF NOT EXISTS sort_order INTEGER NOT NULL DEFAULT 0;
CREATE TABLE IF NOT EXISTS member_overrides (
    id SERIAL PRIMARY KEY,
    employee_id TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL DEFAULT '',
    company TEXT,
    rank TEXT,
    role TEXT,
    department TEXT,
    grp TEXT,
    division TEXT,
    team TEXT,
    cell TEXT,
    work_status TEXT,
    work_time TEXT,
    phone TEXT,
    email TEXT,
    seat_label TEXT,
    photo_url TEXT,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS member_retirements (
    id SERIAL PRIMARY KEY,
    employee_id TEXT,
    name TEXT NOT NULL,
    note TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS member_aliases (
    id SERIAL PRIMARY KEY,
    alias_name TEXT NOT NULL UNIQUE,
    canonical_name TEXT NOT NULL,
    employee_id TEXT,
    note TEXT NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
ALTER TABLE seat_positions ADD COLUMN IF NOT EXISTS seat_map_id INTEGER REFERENCES seat_maps(id) ON DELETE CASCADE;
ALTER TABLE seat_positions ADD COLUMN IF NOT EXISTS seat_slot_id INTEGER;
ALTER TABLE seat_positions ADD COLUMN IF NOT EXISTS row_index INTEGER NOT NULL DEFAULT 0;
ALTER TABLE seat_positions ADD COLUMN IF NOT EXISTS col_index INTEGER NOT NULL DEFAULT 0;
ALTER TABLE seat_positions ADD COLUMN IF NOT EXISTS seat_label TEXT;
ALTER TABLE seat_positions ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS source_type TEXT NOT NULL DEFAULT 'image';
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS source_url TEXT;
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS preview_svg TEXT;
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS view_box_min_x DOUBLE PRECISION;
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS view_box_min_y DOUBLE PRECISION;
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS view_box_width DOUBLE PRECISION;
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS view_box_height DOUBLE PRECISION;
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS image_width INTEGER;
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS image_height INTEGER;
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS cell_gap INTEGER NOT NULL DEFAULT 0;
ALTER TABLE seat_maps ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE seat_maps ALTER COLUMN image_url DROP NOT NULL;

CREATE TABLE IF NOT EXISTS seat_slots (
    id SERIAL PRIMARY KEY,
    seat_map_id INTEGER NOT NULL REFERENCES seat_maps(id) ON DELETE CASCADE,
    slot_key TEXT NOT NULL,
    label TEXT NOT NULL,
    x DOUBLE PRECISION NOT NULL,
    y DOUBLE PRECISION NOT NULL,
    rotation DOUBLE PRECISION NOT NULL DEFAULT 0,
    layer_name TEXT NOT NULL DEFAULT 'chair',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (seat_map_id, slot_key)
);

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = 'seat_positions' AND column_name = 'x'
    ) THEN
        EXECUTE 'UPDATE seat_positions SET row_index = COALESCE(y, row_index, 0), col_index = COALESCE(x, col_index, 0) WHERE seat_map_id IS NULL';
    END IF;
END $$;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = 'seat_positions' AND column_name = 'floor_label'
    ) THEN
        EXECUTE 'UPDATE seat_positions SET seat_label = COALESCE(seat_label, floor_label) WHERE seat_label IS NULL';
    END IF;
END $$;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.columns
        WHERE table_name = 'integration_raw_mh_rows' AND column_name = 'row_values_json'
    ) THEN
        ALTER TABLE integration_raw_mh_rows
        ADD COLUMN row_values_json JSONB NOT NULL DEFAULT '[]'::jsonb;
    END IF;
END $$;

DROP INDEX IF EXISTS seat_positions_map_cell_idx;
CREATE UNIQUE INDEX IF NOT EXISTS seat_positions_map_cell_idx
ON seat_positions (seat_map_id, row_index, col_index)
WHERE seat_map_id IS NOT NULL
  AND seat_slot_id IS NULL;

CREATE UNIQUE INDEX IF NOT EXISTS member_overrides_employee_id_idx
ON member_overrides (employee_id);

CREATE UNIQUE INDEX IF NOT EXISTS member_retirements_name_idx
ON member_retirements (name);

CREATE UNIQUE INDEX IF NOT EXISTS member_aliases_alias_name_idx
ON member_aliases (alias_name);

CREATE UNIQUE INDEX IF NOT EXISTS seat_positions_slot_idx
ON seat_positions (seat_slot_id)
WHERE seat_slot_id IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS integration_raw_organization_rows_batch_row_idx
ON integration_raw_organization_rows (batch_id, row_index);

CREATE UNIQUE INDEX IF NOT EXISTS integration_raw_mh_rows_batch_row_idx
ON integration_raw_mh_rows (batch_id, row_index);

CREATE UNIQUE INDEX IF NOT EXISTS integration_raw_mh_pm_rows_batch_row_idx
ON integration_raw_mh_pm_rows (batch_id, row_index);

CREATE UNIQUE INDEX IF NOT EXISTS integration_raw_payment_rows_batch_row_idx
ON integration_raw_payment_rows (batch_id, row_index);

CREATE INDEX IF NOT EXISTS integration_work_logs_employee_idx
ON integration_work_logs (employee_id, work_date);

CREATE INDEX IF NOT EXISTS integration_work_log_segments_project_idx
ON integration_work_log_segments (project_code, project_name);

CREATE INDEX IF NOT EXISTS integration_vouchers_project_idx
ON integration_vouchers (project_code, project_name);

CREATE UNIQUE INDEX IF NOT EXISTS integration_project_category_mappings_key_idx
ON integration_project_category_mappings (source_key, normalized_project_key);

CREATE UNIQUE INDEX IF NOT EXISTS integration_binary_sources_source_key_idx
ON integration_binary_sources (source_key);

CREATE INDEX IF NOT EXISTS member_versions_member_time_idx
ON member_versions (member_id, valid_from, valid_to);

CREATE INDEX IF NOT EXISTS seat_assignment_versions_member_time_idx
ON seat_assignment_versions (member_id, valid_from, valid_to);

CREATE INDEX IF NOT EXISTS history_revisions_scope_created_idx
ON history_revisions (scope, created_at DESC);

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.table_constraints
        WHERE constraint_name = 'seat_positions_seat_slot_id_fkey'
          AND table_name = 'seat_positions'
    ) THEN
        ALTER TABLE seat_positions
        ADD CONSTRAINT seat_positions_seat_slot_id_fkey
        FOREIGN KEY (seat_slot_id) REFERENCES seat_slots(id) ON DELETE CASCADE;
    END IF;
END $$;

CREATE SCHEMA IF NOT EXISTS auth;

CREATE TABLE IF NOT EXISTS auth.users (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    display_name TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'admin',
    member_id INTEGER NULL REFERENCES members(id) ON DELETE SET NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_from TEXT NOT NULL DEFAULT 'manual',
    last_login_at TIMESTAMPTZ,
    password_changed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS auth.sessions (
    id UUID PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    expires_at TIMESTAMPTZ NOT NULL,
    revoked_at TIMESTAMPTZ,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS auth.login_audit_logs (
    id BIGSERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    user_id BIGINT NULL REFERENCES auth.users(id) ON DELETE SET NULL,
    success BOOLEAN NOT NULL,
    failure_reason TEXT,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS role TEXT NOT NULL DEFAULT 'admin';
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS member_id INTEGER NULL REFERENCES members(id) ON DELETE SET NULL;
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS created_from TEXT NOT NULL DEFAULT 'manual';
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMPTZ;
ALTER TABLE auth.users ADD COLUMN IF NOT EXISTS password_changed_at TIMESTAMPTZ;
ALTER TABLE auth.sessions ADD COLUMN IF NOT EXISTS revoked_at TIMESTAMPTZ;
ALTER TABLE auth.sessions ADD COLUMN IF NOT EXISTS ip_address INET;
ALTER TABLE auth.sessions ADD COLUMN IF NOT EXISTS user_agent TEXT;
ALTER TABLE auth.login_audit_logs ADD COLUMN IF NOT EXISTS user_id BIGINT NULL REFERENCES auth.users(id) ON DELETE SET NULL;
ALTER TABLE auth.login_audit_logs ADD COLUMN IF NOT EXISTS failure_reason TEXT;
ALTER TABLE auth.login_audit_logs ADD COLUMN IF NOT EXISTS ip_address INET;
ALTER TABLE auth.login_audit_logs ADD COLUMN IF NOT EXISTS user_agent TEXT;

DROP INDEX IF EXISTS entity_change_events_entity_idx;
DROP TABLE IF EXISTS entity_change_events;
"""


@contextmanager
def get_conn() -> Iterator[psycopg.Connection]:
    with psycopg.connect(DATABASE_URL, row_factory=dict_row) as conn:
        yield conn


def init_db(max_retries: int = 20, retry_delay: float = 2.0) -> None:
    last_error: Exception | None = None
    for _ in range(max_retries):
        try:
            with get_conn() as conn:
                with conn.cursor() as cur:
                    cur.execute(SCHEMA_SQL)
                    cur.execute(MIGRATION_SQL)
                    ensure_history_backfill(cur)
                conn.commit()
            return
        except psycopg.OperationalError as exc:
            last_error = exc
            time.sleep(retry_delay)
    if last_error is not None:
        raise last_error


def ensure_history_backfill(cur) -> None:
    cur.execute(
        """
        SELECT id
        FROM history_revisions
        WHERE scope = 'organization'
          AND revision_label = 'initial-backfill'
        ORDER BY id ASC
        LIMIT 1
        """
    )
    row = cur.fetchone()
    if row is None:
        cur.execute(
            """
            INSERT INTO history_revisions (scope, revision_label, note)
            VALUES ('organization', 'initial-backfill', 'Seeded from current members and seat_positions state')
            RETURNING id
            """
        )
        revision_id = int(cur.fetchone()["id"])
    else:
        revision_id = int(row["id"])

    cur.execute(
        """
        INSERT INTO member_versions (
            member_id, name, company, rank, role, department, grp, division, team, cell,
            work_status, work_time, phone, email, photo_url,
            valid_from, valid_to, revision_no, changed_by_user_id, change_reason
        )
        SELECT
            m.id, m.name, COALESCE(m.company, ''), COALESCE(m.rank, ''), COALESCE(m.role, ''),
            COALESCE(m.department, ''), COALESCE(m.grp, ''), COALESCE(m.division, ''), COALESCE(m.team, ''), COALESCE(m.cell, ''),
            COALESCE(m.work_status, ''), COALESCE(m.work_time, ''), COALESCE(m.phone, ''), COALESCE(m.email, ''), COALESCE(m.photo_url, ''),
            TIMESTAMPTZ '1970-01-01 00:00:00+00', NULL, %s, NULL, 'initial-backfill'
        FROM members AS m
        WHERE NOT EXISTS (
            SELECT 1
            FROM member_versions mv
            WHERE mv.member_id = m.id
        )
        """,
        (revision_id,),
    )

    cur.execute(
        """
        INSERT INTO seat_assignment_versions (
            member_id, seat_map_id, seat_slot_id, seat_label,
            valid_from, valid_to, revision_no, changed_by_user_id, change_reason
        )
        SELECT
            sp.member_id, sp.seat_map_id, sp.seat_slot_id, COALESCE(sp.seat_label, ''),
            TIMESTAMPTZ '1970-01-01 00:00:00+00', NULL, %s, NULL, 'initial-backfill'
        FROM seat_positions AS sp
        WHERE NOT EXISTS (
            SELECT 1
            FROM seat_assignment_versions sav
            WHERE sav.member_id = sp.member_id
        )
        """,
        (revision_id,),
    )

    cur.execute(
        """
        UPDATE member_versions
        SET valid_from = TIMESTAMPTZ '1970-01-01 00:00:00+00'
        WHERE revision_no = %s
          AND change_reason = 'initial-backfill'
        """,
        (revision_id,),
    )

    cur.execute(
        """
        UPDATE seat_assignment_versions
        SET valid_from = TIMESTAMPTZ '1970-01-01 00:00:00+00'
        WHERE revision_no = %s
          AND change_reason = 'initial-backfill'
        """,
        (revision_id,),
    )