"""
Database layer for Shopfloor Operator Tracking App
SQLite-based, zero external DB server required.
"""
import sqlite3
import os
from datetime import datetime, timedelta

DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "shopfloor.db")

SCHEMA = """
CREATE TABLE IF NOT EXISTS workstations (
    id TEXT PRIMARY KEY,              -- e.g. WKS0001 - a PHYSICAL area/meja/tablet location
    name TEXT NOT NULL,               -- e.g. Meja Winding A1
    group_name TEXT,                  -- Shift group currently assigned e.g. 'A'
    shift_start TEXT,                 -- '07:00'
    shift_end TEXT,                   -- '16:00'
    team_id INTEGER,                  -- assigned team; drives the "Member di Area" list
    active INTEGER DEFAULT 1,         -- 0 = deactivated (kept for history, unselectable)
    product_group TEXT,               -- legacy column, no longer used (moved to routings.product_group)
    sequence INTEGER DEFAULT 0        -- manual sort order for this WKS (used in listings & dashboard cards)
);

-- A routing is a stage/step of work (e.g. "Winding", "Core Assembly") that a
-- Manufacturing Order goes through. One physical workstation (above) can run
-- more than one routing (e.g. a shared bench used for 2 different process
-- steps), and in principle one routing could also be run at more than one
-- workstation - hence the many-to-many link table below.
CREATE TABLE IF NOT EXISTS routings (
    id TEXT PRIMARY KEY,              -- e.g. ROU0008 (usually SAP's own Routing Code)
    name TEXT NOT NULL,               -- e.g. Winding Area
    product_group TEXT,               -- e.g. 'LDT' / 'MPT' - groups routing by product line
    sequence INTEGER DEFAULT 0,       -- manual ordering of process steps within a product_group
    active INTEGER DEFAULT 1          -- 0 = deactivated (kept for history, unselectable)
);

-- Many-to-many: which routing(s) can be worked at which physical workstation.
CREATE TABLE IF NOT EXISTS workstation_routings (
    workstation_id TEXT NOT NULL,
    routing_id TEXT NOT NULL,
    PRIMARY KEY (workstation_id, routing_id),
    FOREIGN KEY(workstation_id) REFERENCES workstations(id),
    FOREIGN KEY(routing_id) REFERENCES routings(id)
);

CREATE TABLE IF NOT EXISTS teams (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT UNIQUE,                 -- short code e.g. 'CST', 'WLV' (SPR = Superuser/admin team)
    name TEXT NOT NULL UNIQUE,
    leader_nik TEXT,                  -- FK employees.nik
    supervisor_nik TEXT,               -- FK employees.nik (role='supervisor') - who oversees this team
    active INTEGER DEFAULT 1,         -- 0 = deactivated (kept for history, unselectable)
    FOREIGN KEY(leader_nik) REFERENCES employees(nik),
    FOREIGN KEY(supervisor_nik) REFERENCES employees(nik)
);

-- Shift groups (A/B/C rotate through the 3 standard shifts; Z = "ALL", used
-- for admin/superuser accounts that aren't tied to a single rotating group).
CREATE TABLE IF NOT EXISTS groups (
    code TEXT PRIMARY KEY,            -- 'A' / 'B' / 'C' / 'Z'
    name TEXT NOT NULL                -- 'Group A' / ... / 'ALL'
);

CREATE TABLE IF NOT EXISTS employees (
    nik TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    group_name TEXT NOT NULL,         -- FK groups.code - 'A'/'B'/'C' (shift group) or 'Z' (ALL)
    team_id INTEGER,                  -- assigned team (master team)
    role TEXT DEFAULT 'operator',     -- 'operator' / 'leader' / 'supervisor' / 'administrator'
    active INTEGER DEFAULT 1,
    FOREIGN KEY(team_id) REFERENCES teams(id)
);

CREATE TABLE IF NOT EXISTS manufacturing_orders (
    mo_number TEXT PRIMARY KEY,
    description TEXT,
    workstation_id TEXT,               -- legacy column, no longer used (moved to routing_id)
    routing_id TEXT,                   -- which routing/stage this MO belongs to
    status TEXT DEFAULT 'Active',      -- MO master status: Active / On Hold / Completed / Cancel
    ideal_time_minutes INTEGER DEFAULT 0, -- planned/ideal cycle time for reporting
    active INTEGER DEFAULT 1,             -- 0 = deactivated (kept for history, unselectable)
    created_at TEXT DEFAULT (datetime('now','localtime')),
    -- Reference fields synced from SAP (never drive the shopfloor workflow directly)
    project_code TEXT,
    so_number TEXT,
    trafo_id TEXT,
    sn TEXT,
    sap_status TEXT,                      -- SAP's own MO Status code (RL/ST/CL/...)
    status_production TEXT,               -- SAP 'Status Production' (Open/Closed)
    required_date TEXT,
    planned_start_date TEXT,
    planned_end_date TEXT,
    ship_date TEXT,
    recommendation_start_date TEXT,
    recommendation_end_date TEXT,
    sap_updated_at TEXT
);

-- Reference data synced from SAP: one row per Project (a project groups many MOs)
CREATE TABLE IF NOT EXISTS projects (
    project_code TEXT PRIMARY KEY,
    project_name TEXT,
    start_date TEXT,
    finish_date TEXT,
    updated_at TEXT
);

-- Reference data synced from SAP: material requirement/readiness lines per MO.
-- row_key is a stable hash of the natural business key so re-importing the
-- same SAP export updates existing lines instead of duplicating them.
CREATE TABLE IF NOT EXISTS mo_materials (
    row_key TEXT PRIMARY KEY,
    mo_number TEXT,
    item_code TEXT,
    material_name TEXT,
    specification TEXT,
    group_name TEXT,
    sub_group TEXT,
    procurement_method TEXT,
    lead_time TEXT,
    latest_order_date TEXT,
    warehouse TEXT,
    required_qty REAL,
    issued_qty REAL,
    remaining_need REAL,
    on_hand REAL,
    is_commited REAL,
    available_stock REAL,
    po_number TEXT,
    total_po_qty REAL,
    po_eta TEXT,
    prev_cum_need REAL,
    curr_cum_need REAL,
    status TEXT,
    updated_at TEXT
);
CREATE INDEX IF NOT EXISTS idx_materials_mo ON mo_materials(mo_number);

-- Small key-value store for app-wide settings (e.g. last SAP import info)
CREATE TABLE IF NOT EXISTS app_settings (
    key TEXT PRIMARY KEY,
    value TEXT
);

-- The 3 standard plant shifts. Every session's "expected end time" for the
-- forgotten-clock-out sweep is derived from whichever shift its login time
-- falls into - not a fixed per-workstation value - so it works correctly
-- across all 3 shifts on any routing.
CREATE TABLE IF NOT EXISTS shifts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    start_time TEXT NOT NULL,   -- 'HH:MM'
    end_time TEXT NOT NULL      -- 'HH:MM'; '00:00' means midnight (wraps to next day)
);

-- One row per operator "shift session" at a workstation (login -> logout)
CREATE TABLE IF NOT EXISTS work_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    nik TEXT NOT NULL,
    workstation_id TEXT NOT NULL,     -- physical WKS the operator is standing at
    routing_id TEXT,                  -- which routing/stage they're working (from the chosen MO)
    mo_number TEXT NOT NULL,
    login_time TEXT NOT NULL,
    logout_time TEXT,
    status TEXT NOT NULL DEFAULT 'On Duty',   -- current card location
    is_open INTEGER NOT NULL DEFAULT 1,       -- 1 while session active
    is_overtime INTEGER NOT NULL DEFAULT 0,   -- 1 = exempt from auto-close sweep (sanctioned lembur)
    FOREIGN KEY(nik) REFERENCES employees(nik),
    FOREIGN KEY(mo_number) REFERENCES manufacturing_orders(mo_number)
);

-- One row per activity segment (On Duty / Toilet / Istirahat / ... )
CREATE TABLE IF NOT EXISTS activity_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id INTEGER NOT NULL,
    nik TEXT NOT NULL,
    activity TEXT NOT NULL,
    start_time TEXT NOT NULL,
    end_time TEXT,
    note TEXT,
    FOREIGN KEY(session_id) REFERENCES work_sessions(id)
);

CREATE INDEX IF NOT EXISTS idx_sessions_open ON work_sessions(is_open);
CREATE INDEX IF NOT EXISTS idx_activity_session ON activity_log(session_id);

-- Locks a workstation to a single tablet/device at a time, so the same
-- routing/operation cannot be opened for time-booking on two tablets at once.
CREATE TABLE IF NOT EXISTS workstation_claims (
    workstation_id TEXT PRIMARY KEY,
    client_token TEXT NOT NULL,
    claimed_at TEXT NOT NULL,
    last_heartbeat TEXT NOT NULL,
    FOREIGN KEY(workstation_id) REFERENCES workstations(id)
);
"""

# A claim is considered stale (tablet closed/lost connection) if no heartbeat
# has been received within this many seconds - the station then becomes free.
CLAIM_STALE_SECONDS = 45

# Reusable ORDER BY fragments.
# Physical workstations sort by their manual sequence number first (so
# dashboard cards / lists line up the way the plant is laid out), then name.
WORKSTATION_ORDER_SQL = "(sequence IS NULL OR sequence=0), sequence, name"
# Routing lists show up neatly grouped by product line (e.g. LDT / MPT) and
# in process sequence within each group. Routings without a product_group
# sort to the bottom, not the top.
ROUTING_ORDER_SQL = "(product_group IS NULL OR product_group=''), product_group, sequence, name"

# Activities that pull an operator OFF the "On Duty" board into another lane.
AWAY_ACTIVITIES = [
    "Ke Toilet",
    "Istirahat",
    "Shalat",
    "Menunggu Perbaikan",
    "Menunggu Material",
    "Menunggu Testing",
]
# Terminal activities that end the work session entirely.
END_ACTIVITIES = ["Selesai Bekerja", "MO Selesai"]
ALL_ACTIVITIES = AWAY_ACTIVITIES + END_ACTIVITIES

# System-triggered status (never shown as a manual activity-picker button):
# set automatically on an operator's HOME session when they're logged in at a
# DIFFERENT workstation while still on duty at home - i.e. temporarily loaned
# out to help elsewhere. Their home session stays open (parked, not counted
# as worked time) so a single tap on "Lanjut Bekerja" resumes it later.
LOAN_STATUS = "Dipinjam ke Routing Lain"
# All statuses a work_session can carry / a board lane can show.
ALL_LANE_STATUSES = ["On Duty"] + AWAY_ACTIVITIES + [LOAN_STATUS]

# MO master-data status: controls whether an MO can be selected by operators
# on the shopfloor at all - independent from the activity-log/session data.
# Only "Active" MOs are selectable; On Hold/Completed/Cancel are not.
MO_STATUSES = ["Active", "On Hold", "Completed", "Cancel"]
MO_SELECTABLE_STATUS = "Active"

# Forgotten clock-out handling: a session still open this many minutes past
# its workstation's scheduled shift_end is flagged "overdue" on the board so
# a leader notices early; past AUTO_CLOSE_GRACE_HOURS it's force-closed by
# the sweep so it never bleeds into the next day and blocks a fresh login.
OVERDUE_WARNING_MINUTES = 30
AUTO_CLOSE_GRACE_HOURS = 1
AUTO_CLOSED_STATUS = "Selesai Otomatis (Lupa Tap)"
FORCE_CLOSED_STATUS = "Selesai Dipaksa Supervisor"


def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def init_db(seed=True):
    fresh = not os.path.exists(DB_PATH)
    conn = get_db()
    conn.executescript(SCHEMA)
    conn.commit()
    _migrate(conn)
    if fresh and seed:
        _seed(conn)
    conn.close()


def _migrate(conn):
    """Safe additive migration: adds columns if this DB was created by an
    older version of the app that didn't have these features yet."""
    def ensure(table, col, ddl):
        cols = [r["name"] for r in conn.execute(f"PRAGMA table_info({table})").fetchall()]
        if col not in cols:
            conn.execute(f"ALTER TABLE {table} ADD COLUMN {ddl}")

    ensure("employees", "team_id", "team_id INTEGER")
    ensure("employees", "role", "role TEXT DEFAULT 'operator'")
    ensure("employees", "active", "active INTEGER DEFAULT 1")
    ensure("workstations", "team_id", "team_id INTEGER")
    ensure("workstations", "active", "active INTEGER DEFAULT 1")
    ensure("workstations", "product_group", "product_group TEXT")
    ensure("workstations", "sequence", "sequence INTEGER DEFAULT 0")
    ensure("teams", "active", "active INTEGER DEFAULT 1")
    ensure("manufacturing_orders", "active", "active INTEGER DEFAULT 1")
    ensure("manufacturing_orders", "routing_id", "routing_id TEXT")
    ensure("manufacturing_orders", "project_code", "project_code TEXT")
    ensure("manufacturing_orders", "so_number", "so_number TEXT")
    ensure("manufacturing_orders", "trafo_id", "trafo_id TEXT")
    ensure("manufacturing_orders", "sn", "sn TEXT")
    ensure("manufacturing_orders", "sap_status", "sap_status TEXT")
    ensure("manufacturing_orders", "status_production", "status_production TEXT")
    ensure("manufacturing_orders", "required_date", "required_date TEXT")
    ensure("manufacturing_orders", "planned_start_date", "planned_start_date TEXT")
    ensure("manufacturing_orders", "planned_end_date", "planned_end_date TEXT")
    ensure("manufacturing_orders", "ship_date", "ship_date TEXT")
    ensure("manufacturing_orders", "recommendation_start_date", "recommendation_start_date TEXT")
    ensure("manufacturing_orders", "recommendation_end_date", "recommendation_end_date TEXT")
    ensure("manufacturing_orders", "sap_updated_at", "sap_updated_at TEXT")
    ensure("activity_log", "note", "note TEXT")
    ensure("work_sessions", "is_overtime", "is_overtime INTEGER NOT NULL DEFAULT 0")
    ensure("work_sessions", "routing_id", "routing_id TEXT")
    ensure("teams", "supervisor_nik", "supervisor_nik TEXT")
    ensure("teams", "code", "code TEXT")
    conn.commit()

    # Seed the master Group list (A/B/C/Z) if empty - idempotent, safe to
    # run on every startup.
    conn.executemany(
        "INSERT OR IGNORE INTO groups (code, name) VALUES (?,?)",
        [("A", "Group A"), ("B", "Group B"), ("C", "Group C"), ("Z", "ALL")],
    )
    conn.commit()

    # One-time upgrade path: older DBs (pre "WKS vs Routing" split) had
    # `workstations` doubling as the routing master, with MOs and sessions
    # pointing at it via workstation_id. If (and only if) there's real
    # evidence of that old model actually being in use - an MO or session
    # already pointing at a workstation_id with no routing_id set - mirror
    # each workstation into `routings` 1:1, link them, and backfill
    # routing_id everywhere, so upgrading never loses data.
    #
    # IMPORTANT: this must NOT simply check "routings is empty" - a brand
    # new install seeded with physical WKS but no Routing yet (Routing master
    # is meant to come from the first SAP import) also has an empty
    # `routings` table at that point, and would otherwise be wrongly treated
    # as "old data to migrate", cloning WKS codes into fake Routing rows.
    needs_backfill = conn.execute(
        """SELECT 1 FROM manufacturing_orders WHERE workstation_id IS NOT NULL AND routing_id IS NULL
           UNION ALL
           SELECT 1 FROM work_sessions WHERE workstation_id IS NOT NULL AND routing_id IS NULL
           LIMIT 1"""
    ).fetchone()
    if needs_backfill:
        old_workstations = conn.execute("SELECT * FROM workstations").fetchall()
        for ws in old_workstations:
            conn.execute(
                "INSERT OR IGNORE INTO routings (id, name, product_group, sequence, active) VALUES (?,?,?,?,?)",
                (ws["id"], ws["name"], ws["product_group"], ws["sequence"] or 0, ws["active"]),
            )
            conn.execute(
                "INSERT OR IGNORE INTO workstation_routings (workstation_id, routing_id) VALUES (?,?)",
                (ws["id"], ws["id"]),
            )
        conn.execute(
            "UPDATE manufacturing_orders SET routing_id=workstation_id WHERE routing_id IS NULL AND workstation_id IS NOT NULL"
        )
        conn.execute(
            "UPDATE work_sessions SET routing_id=workstation_id WHERE routing_id IS NULL AND workstation_id IS NOT NULL"
        )
        conn.commit()

    # Seed the 3 standard shifts if the table is empty (fresh or upgraded DB)
    if not conn.execute("SELECT 1 FROM shifts LIMIT 1").fetchone():
        conn.executemany(
            "INSERT INTO shifts (id, name, start_time, end_time) VALUES (?,?,?,?)",
            [
                (1, "Shift 1", "07:00", "16:00"),
                (2, "Shift 2", "16:00", "00:00"),
                (3, "Shift 3", "00:00", "07:00"),
            ],
        )
        conn.commit()

    # Data migration: old status values -> new MO status vocabulary
    # (Active / On Hold / Completed / Cancel). Only "Active" MOs can be
    # selected by operators on the shopfloor.
    conn.execute("UPDATE manufacturing_orders SET status='Active' WHERE status='On Progress'")
    conn.commit()


def _seed(conn):
    cur = conn.cursor()

    # ---------------- Teams (with short codes) ----------------
    # SPR = Superuser/admin team (SAP-side accounts); the 8 production teams
    # map 1:1 to a physical Work Center below.
    teams = [
        ("SPR", "Superuser"),
        ("CST", "Core Stacking"),
        ("WLV", "Winding LV"),
        ("WHV", "Winding HV"),
        ("WVR", "Winding HVR"),
        ("CPP", "CCA + Upperyoke"),
        ("CON", "Connection"),
        ("FNA", "Final Assembly"),
        ("FNG", "Finishing"),
    ]
    cur.executemany("INSERT OR IGNORE INTO teams (code, name) VALUES (?,?)", teams)
    team_id_by_code = {
        row["code"]: row["id"]
        for row in cur.execute("SELECT id, code FROM teams").fetchall()
    }

    # ---------------- Employees ----------------
    # (nik, name, group_code, team_code, role)
    employees = [
        # -- SAP / admin accounts: not tied to a production team --
        ("SAP1001", "Budi Susatyo", "Z", "SPR", "administrator"),
        ("SAP1002", "Administrator", "Z", "SPR", "administrator"),
        ("SAP1003", "Supervisor", "Z", "SPR", "supervisor"),
        # -- Group A operators --
        ("TPI2001", "Nurhadi Anwar", "A", "CST", "operator"),
        ("TPI2002", "Shofian Nudin", "A", "CST", "operator"),
        ("TPI2003", "M. Afrian", "A", "CST", "operator"),
        ("TPI2004", "Jukhaeri", "A", "CST", "operator"),
        ("TPI2005", "Hery Arifin", "A", "WLV", "operator"),
        ("TPI2006", "Suprayogi", "A", "WLV", "operator"),
        ("TPI2007", "Wildhan Mahendra Putra", "A", "WHV", "operator"),
        ("TPI2008", "Wakhidin", "A", "WHV", "operator"),
        ("TPI2009", "Sahrul", "A", "WVR", "operator"),
        ("TPI2010", "Wasito", "A", "WVR", "operator"),
        ("TPI2011", "Shoni Ramdhani", "A", "WVR", "operator"),
        ("TPI2012", "Rohmat Abdullah", "A", "WVR", "operator"),
        ("TPI2013", "Achmad Idrus", "A", "CPP", "operator"),
        ("TPI2014", "Heri Suryana", "A", "CPP", "operator"),
        ("TPI2015", "Arief Rahman", "A", "CPP", "operator"),
        ("TPI2016", "Sanan", "A", "CON", "operator"),
        ("TPI2017", "Akhmad Aji Fakhrudin", "A", "CON", "operator"),
        ("TPI2018", "Jaenal Arifin", "A", "CON", "operator"),
        ("TPI2019", "Hendri Ali (T)", "A", "CON", "operator"),
        ("TPI2020", "Muhammad Kamil Mughni (T)", "A", "CON", "operator"),
        ("TPI2021", "Erwin Maulana (T)", "A", "CON", "operator"),
        ("TPI2022", "Riki Dwi Susanto", "A", "FNA", "operator"),
        ("TPI2023", "Andi Nurwakhid", "A", "FNA", "operator"),
        ("TPI2024", "Muhamad Yusuf", "A", "FNA", "operator"),
        ("TPI2025", "Agus Setiono", "A", "FNA", "operator"),
        ("TPI2026", "Abdul Muhit", "A", "FNA", "operator"),
        ("TPI2027", "Raflis", "A", "FNG", "operator"),
        ("TPI2028", "Hardiansyah", "A", "FNG", "operator"),
        # -- Group B operators --
        ("TPI2098", "Dasikun", "B", "CST", "operator"),
        ("TPI2099", "Aji Nugroho Ichsan", "B", "CST", "operator"),
        ("TPI2100", "Abdul Rojak", "B", "CST", "operator"),
        ("TPI2101", "Arya Firgiansah", "B", "CST", "operator"),
        ("TPI2102", "Sundowi", "B", "WLV", "operator"),
        ("TPI2103", "Endar Suhendar", "B", "WLV", "operator"),
        ("TPI2104", "Muhammad Maulana", "B", "WHV", "operator"),
        ("TPI2105", "Angga Sutrisno", "B", "WHV", "operator"),
        ("TPI2106", "Priadi", "B", "WVR", "operator"),
        ("TPI2107", "Wahyudin Muin", "B", "WVR", "operator"),
        ("TPI2108", "Denis Sapta Muharam", "B", "WVR", "operator"),
        ("TPI2109", "Bambang Tentrem Suprihatin", "B", "CPP", "operator"),
        ("TPI2110", "Adi Bambang Aryanto", "B", "CPP", "operator"),
        ("TPI2111", "Sapto", "B", "CPP", "operator"),
        ("TPI2112", "Muhammad Heri Hastomo", "B", "CON", "operator"),
        ("TPI2113", "Agung", "B", "CON", "operator"),
        ("TPI2114", "Lukman Hakim", "B", "CON", "operator"),
        ("TPI2115", "Azwar Harris Pamusty (T)", "B", "CON", "operator"),
        ("TPI2116", "Royong Saputra (T)", "B", "CON", "operator"),
        ("TPI2117", "Iqbal Rusdianto (T)", "B", "CON", "operator"),
        ("TPI2118", "Nawawi", "B", "FNA", "operator"),
        ("TPI2119", "Adlan Fahmi", "B", "FNA", "operator"),
        ("TPI2120", "Anton Triatno", "B", "FNA", "operator"),
        ("TPI2121", "Mustakim", "B", "FNA", "operator"),
        ("TPI2122", "Fatkur Roziq", "B", "FNA", "operator"),
        ("TPI2123", "Tri Pamungkas", "B", "FNG", "operator"),
    ]
    for nik, name, group_code, team_code, role in employees:
        cur.execute(
            "INSERT OR IGNORE INTO employees (nik, name, group_name, team_id, role) VALUES (?,?,?,?,?)",
            (nik, name, group_code, team_id_by_code.get(team_code), role),
        )

    # SAP1003 ("Supervisor") oversees the Superuser team by default; the 8
    # production teams are left without a leader/supervisor assigned - an
    # Administrator/Supervisor should assign the real one via Master Team.
    cur.execute("UPDATE teams SET supervisor_nik='SAP1003' WHERE code='SPR'")

    # ---------------- Work Centers (physical WKS) ----------------
    # Sequence numbers below match the "Seq" column of the reference list, so
    # these line up in that same order everywhere (dashboard cards, lists).
    # No Routing is linked yet (the source list's Routing column is blank) -
    # link the real routing code(s) per WKS via Master Routing once known.
    workstations = [
        # (id, name, sequence, team_code)
        ("WK0001", "Core Stacking", 1, "CST"),
        ("WK0002", "Winding LV", 2, "WLV"),
        ("WK0003", "Winding HV", 3, "WHV"),
        ("WK0004", "Winding HVR", 4, "WVR"),
        ("WK0005", "CCA + Upperyoke", 5, "CPP"),
        ("WK0006", "Connection", 6, "CON"),
        ("WK0007", "Final Assembly", 7, "FNA"),
        ("WK0008", "Finishing", 8, "FNG"),
    ]
    for wsid, name, seq, team_code in workstations:
        cur.execute(
            """INSERT OR IGNORE INTO workstations
               (id, name, group_name, shift_start, shift_end, team_id, sequence, active)
               VALUES (?,?,?,?,?,?,?,1)""",
            (wsid, name, "A", "07:00", "16:00", team_id_by_code.get(team_code), seq),
        )
    conn.commit()


def now_str():
    return datetime.now().strftime("%Y-%m-%d %H:%M:%S")


def find_open_session_at(conn, nik, workstation_id):
    """The open session for this employee AT a specific workstation, if any.
    Scoping by station (not just nik) is what lets one person have a parked
    session at their home station and a genuinely active one elsewhere."""
    return conn.execute(
        "SELECT * FROM work_sessions WHERE nik=? AND workstation_id=? AND is_open=1 ORDER BY id DESC LIMIT 1",
        (nik, workstation_id),
    ).fetchone()


def find_open_sessions_elsewhere(conn, nik, exclude_workstation_id):
    """Any OTHER open session(s) this employee has, at a different workstation."""
    return conn.execute(
        """SELECT ws.*, w.name as workstation_name FROM work_sessions ws
           JOIN workstations w ON w.id = ws.workstation_id
           WHERE ws.nik=? AND ws.is_open=1 AND ws.workstation_id!=?""",
        (nik, exclude_workstation_id),
    ).fetchall()


# --------------------------------------------------------------------------
# Forgotten clock-out handling.
# --------------------------------------------------------------------------
def _minutes_of_day(hhmm):
    h, m = map(int, hhmm.split(":")[:2])
    return h * 60 + m


def get_shifts(conn):
    return conn.execute("SELECT * FROM shifts ORDER BY id").fetchall()


def find_shift_for_datetime(conn, dt):
    """Which of the 3 standard shifts (Shift 1/2/3) this datetime's
    time-of-day falls into, based on the shifts master table."""
    t = dt.hour * 60 + dt.minute
    for s in get_shifts(conn):
        start = _minutes_of_day(s["start_time"])
        end = _minutes_of_day(s["end_time"])
        if end == 0:
            end = 24 * 60  # '00:00' end means midnight = end of day
        if start <= t < end:
            return s
    return None


def session_shift_cutoff(conn, login_time_str):
    """The datetime this session's ACTUAL shift (Shift 1/2/3, determined
    from when the operator logged in - not a fixed per-workstation value)
    is scheduled to end. Handles the day rollover for Shift 2 (ends at
    midnight) correctly."""
    login_dt = datetime.strptime(login_time_str, "%Y-%m-%d %H:%M:%S")
    shift = find_shift_for_datetime(conn, login_dt)
    if not shift:
        return None
    h, m = map(int, shift["end_time"].split(":")[:2])
    cutoff = login_dt.replace(hour=h, minute=m, second=0, microsecond=0)
    if cutoff <= login_dt:
        cutoff += timedelta(days=1)
    return cutoff


def session_overdue_info(conn, login_time_str, now=None):
    """Returns (is_overdue, minutes_past_shift_end) for the board's warning
    badge - fires OVERDUE_WARNING_MINUTES after the scheduled shift end,
    well before the sweep would auto-close it."""
    cutoff = session_shift_cutoff(conn, login_time_str)
    if not cutoff:
        return False, 0
    now = now or datetime.now()
    minutes_past = (now - cutoff).total_seconds() / 60
    return minutes_past > OVERDUE_WARNING_MINUTES, max(0, int(minutes_past))


def sweep_stale_sessions(conn, grace_hours=AUTO_CLOSE_GRACE_HOURS):
    """Auto-closes any open session sitting AUTO_CLOSE_GRACE_HOURS or more
    past its OWN shift's (Shift 1/2/3, from its login time) scheduled end -
    handles the classic forgotten-clock-out so the operator isn't locked out
    the next shift and reports don't inflate with hours nobody actually
    worked. Sessions flagged is_overtime are skipped entirely - a Leader or
    Supervisor has vouched the extended hours are real, sanctioned lembur.
    The recorded end time is capped at the scheduled shift end itself (the
    fairest available estimate), not "now". Call this cheaply and often (it
    only does work when something is actually overdue) - it's wired into
    the board refresh and login endpoints so it runs continuously without a
    separate cron job.
    Returns the list of session ids that were closed."""
    now = datetime.now()
    open_sessions = conn.execute(
        "SELECT * FROM work_sessions WHERE is_open=1 AND is_overtime=0"
    ).fetchall()
    closed_ids = []
    for s in open_sessions:
        cutoff = session_shift_cutoff(conn, s["login_time"])
        if not cutoff:
            continue
        if now < cutoff + timedelta(hours=grace_hours):
            continue
        cutoff_str = cutoff.strftime("%Y-%m-%d %H:%M:%S")
        conn.execute(
            "UPDATE activity_log SET end_time=? WHERE session_id=? AND end_time IS NULL",
            (cutoff_str, s["id"]),
        )
        conn.execute(
            "UPDATE work_sessions SET is_open=0, logout_time=?, status=? WHERE id=?",
            (cutoff_str, AUTO_CLOSED_STATUS, s["id"]),
        )
        conn.execute(
            """INSERT INTO activity_log (session_id, nik, activity, start_time, end_time, note)
               VALUES (?,?,?,?,?,?)""",
            (
                s["id"], s["nik"], AUTO_CLOSED_STATUS, cutoff_str, cutoff_str,
                f"Sesi tidak pernah ditutup manual; otomatis diselesaikan {grace_hours} jam setelah "
                f"jadwal shift berakhir ({cutoff_str[11:16]}).",
            ),
        )
        closed_ids.append(s["id"])
    if closed_ids:
        conn.commit()
    return closed_ids


def force_end_session(conn, session_id, actor):
    """Leader/Supervisor manually ends someone else's stuck session right
    now, from the admin panel. Records who did it for accountability."""
    now_s = now_str()
    session = conn.execute("SELECT * FROM work_sessions WHERE id=? AND is_open=1", (session_id,)).fetchone()
    if not session:
        return False
    conn.execute(
        "UPDATE activity_log SET end_time=? WHERE session_id=? AND end_time IS NULL",
        (now_s, session_id),
    )
    conn.execute(
        "UPDATE work_sessions SET is_open=0, logout_time=?, status=? WHERE id=?",
        (now_s, FORCE_CLOSED_STATUS, session_id),
    )
    conn.execute(
        """INSERT INTO activity_log (session_id, nik, activity, start_time, end_time, note)
           VALUES (?,?,?,?,?,?)""",
        (
            session_id, session["nik"], FORCE_CLOSED_STATUS, now_s, now_s,
            f"Ditutup paksa oleh {actor['name']} (NIK {actor['nik']}, {actor['role']}).",
        ),
    )
    conn.commit()
    return True


def set_overtime(conn, session_id, actor, overtime):
    """Leader/Supervisor marks (or unmarks) a session as sanctioned lembur,
    exempting it from the auto-close sweep. Records who did it and when."""
    session = conn.execute("SELECT * FROM work_sessions WHERE id=? AND is_open=1", (session_id,)).fetchone()
    if not session:
        return False
    conn.execute("UPDATE work_sessions SET is_overtime=? WHERE id=?", (1 if overtime else 0, session_id))
    now_s = now_str()
    marker = "Ditandai Lembur" if overtime else "Tanda Lembur Dibatalkan"
    conn.execute(
        """INSERT INTO activity_log (session_id, nik, activity, start_time, end_time, note)
           VALUES (?,?,?,?,?,?)""",
        (
            session_id, session["nik"], marker, now_s, now_s,
            f"{marker} oleh {actor['name']} (NIK {actor['nik']}, {actor['role']}).",
        ),
    )
    conn.commit()
    return True


def worked_seconds_for_mo(conn, mo_number, date_str=None):
    """Total cumulative 'On Duty' seconds logged against a MO.
    If date_str (YYYY-MM-DD) is given, restrict to activity segments that
    started on that date; otherwise sum across the MO's whole lifetime."""
    q = """SELECT a.start_time, a.end_time FROM activity_log a
           JOIN work_sessions ws ON ws.id = a.session_id
           WHERE a.activity='On Duty' AND ws.mo_number=?"""
    params = [mo_number]
    if date_str:
        q += " AND substr(a.start_time,1,10)=?"
        params.append(date_str)
    rows = conn.execute(q, params).fetchall()
    now = datetime.now()
    total = 0.0
    for r in rows:
        start = datetime.strptime(r["start_time"], "%Y-%m-%d %H:%M:%S")
        end = datetime.strptime(r["end_time"], "%Y-%m-%d %H:%M:%S") if r["end_time"] else now
        total += (end - start).total_seconds()
    return int(total)


# --------------------------------------------------------------------------
# Workstation claims: one tablet "holds" a workstation at a time.
# --------------------------------------------------------------------------
def _claim_is_stale(claim_row):
    last_hb = datetime.strptime(claim_row["last_heartbeat"], "%Y-%m-%d %H:%M:%S")
    return (datetime.now() - last_hb).total_seconds() > CLAIM_STALE_SECONDS


def get_active_claim(conn, workstation_id):
    """Returns the claim row if the workstation is currently held by a live
    tablet, or None if it's free (never claimed, released, or stale)."""
    row = conn.execute(
        "SELECT * FROM workstation_claims WHERE workstation_id=?", (workstation_id,)
    ).fetchone()
    if row is None:
        return None
    if _claim_is_stale(row):
        return None
    return row


def claim_station(conn, workstation_id, client_token):
    """Try to claim a workstation for this client_token.
    Returns (ok, message). Succeeds if free, or already held by the same token."""
    existing = get_active_claim(conn, workstation_id)
    now = now_str()
    if existing is not None and existing["client_token"] != client_token:
        return False, "Workstation ini sedang digunakan tablet lain"
    conn.execute(
        """INSERT INTO workstation_claims (workstation_id, client_token, claimed_at, last_heartbeat)
           VALUES (?,?,?,?)
           ON CONFLICT(workstation_id) DO UPDATE SET
             client_token=excluded.client_token,
             claimed_at=CASE WHEN workstation_claims.client_token=excluded.client_token
                             THEN workstation_claims.claimed_at ELSE excluded.claimed_at END,
             last_heartbeat=excluded.last_heartbeat""",
        (workstation_id, client_token, now, now),
    )
    conn.commit()
    return True, "ok"


def heartbeat_station(conn, workstation_id, client_token):
    row = conn.execute(
        "SELECT * FROM workstation_claims WHERE workstation_id=? AND client_token=?",
        (workstation_id, client_token),
    ).fetchone()
    if row is None:
        return False
    conn.execute(
        "UPDATE workstation_claims SET last_heartbeat=? WHERE workstation_id=? AND client_token=?",
        (now_str(), workstation_id, client_token),
    )
    conn.commit()
    return True


def release_station(conn, workstation_id, client_token):
    conn.execute(
        "DELETE FROM workstation_claims WHERE workstation_id=? AND client_token=?",
        (workstation_id, client_token),
    )
    conn.commit()


# --------------------------------------------------------------------------
# Teams
# --------------------------------------------------------------------------
def get_team_with_members(conn, team_id):
    """Returns {id, name, leader_nik, leader_name, members:[...]} or None."""
    if not team_id:
        return None
    team = conn.execute("SELECT * FROM teams WHERE id=?", (team_id,)).fetchone()
    if not team:
        return None
    leader = None
    if team["leader_nik"]:
        leader = conn.execute("SELECT name FROM employees WHERE nik=?", (team["leader_nik"],)).fetchone()
    members = conn.execute(
        "SELECT * FROM employees WHERE team_id=? AND active=1 ORDER BY name", (team_id,)
    ).fetchall()
    return {
        "id": team["id"],
        "name": team["name"],
        "leader_nik": team["leader_nik"],
        "leader_name": leader["name"] if leader else None,
        "members": [dict(m) for m in members],
    }


# --------------------------------------------------------------------------
# Admin access control: NIK-based, no passwords - matches how operators
# already identify themselves. 'administrator' always has full access.
# Otherwise: only 'leader'/'supervisor' can edit/delete master data; only
# 'supervisor' can create new master data entries.
# --------------------------------------------------------------------------
def get_actor(conn, nik):
    """Look up the employee (must be active) making an admin request."""
    if not nik:
        return None
    return conn.execute("SELECT * FROM employees WHERE nik=? AND active=1", (str(nik).strip(),)).fetchone()


def check_role(conn, nik, allowed_roles):
    """Returns (ok: bool, message: str, actor_row_or_None).
    'administrator' bypasses allowed_roles entirely - it's the superset role
    that can manage all master data, edit/update everything, and run SAP
    imports, regardless of which finer-grained roles a given action lists."""
    actor = get_actor(conn, nik)
    if not actor:
        return False, "NIK admin tidak dikenali atau tidak aktif. Silakan masuk kembali.", None
    if actor["role"] == "administrator":
        return True, "ok", actor
    if actor["role"] not in allowed_roles:
        role_label = " atau ".join(r.capitalize() for r in allowed_roles + ["administrator"])
        return False, f"Aksi ini hanya bisa dilakukan oleh {role_label}.", actor
    return True, "ok", actor


# --------------------------------------------------------------------------
# Usage checks: decide whether a master-data record can be hard-deleted, or
# must only be soft-deactivated because it's already referenced by real
# operational/transactional data.
# --------------------------------------------------------------------------
def employee_has_transactions(conn, nik):
    c = conn.execute("SELECT COUNT(*) c FROM work_sessions WHERE nik=?", (nik,)).fetchone()["c"]
    return c > 0


def employee_is_team_leader(conn, nik):
    c = conn.execute("SELECT COUNT(*) c FROM teams WHERE leader_nik=?", (nik,)).fetchone()["c"]
    return c > 0


def team_is_referenced(conn, team_id):
    c1 = conn.execute("SELECT COUNT(*) c FROM employees WHERE team_id=?", (team_id,)).fetchone()["c"]
    c2 = conn.execute("SELECT COUNT(*) c FROM workstations WHERE team_id=?", (team_id,)).fetchone()["c"]
    return (c1 + c2) > 0


def workstation_is_referenced(conn, wsid):
    c1 = conn.execute("SELECT COUNT(*) c FROM work_sessions WHERE workstation_id=?", (wsid,)).fetchone()["c"]
    c2 = conn.execute("SELECT COUNT(*) c FROM workstation_routings WHERE workstation_id=?", (wsid,)).fetchone()["c"]
    return (c1 + c2) > 0


def routing_is_referenced(conn, routing_id):
    c1 = conn.execute("SELECT COUNT(*) c FROM manufacturing_orders WHERE routing_id=?", (routing_id,)).fetchone()["c"]
    c2 = conn.execute("SELECT COUNT(*) c FROM work_sessions WHERE routing_id=?", (routing_id,)).fetchone()["c"]
    return (c1 + c2) > 0


def get_routing_workstation_ids(conn, routing_id):
    """List of workstation ids this routing is currently linked to."""
    rows = conn.execute(
        "SELECT workstation_id FROM workstation_routings WHERE routing_id=?", (routing_id,)
    ).fetchall()
    return [r["workstation_id"] for r in rows]


def set_routing_workstations(conn, routing_id, workstation_ids):
    """Replaces the full set of workstations this routing is linked to."""
    conn.execute("DELETE FROM workstation_routings WHERE routing_id=?", (routing_id,))
    for wsid in workstation_ids:
        wsid = str(wsid).strip()
        if not wsid:
            continue
        conn.execute(
            "INSERT OR IGNORE INTO workstation_routings (workstation_id, routing_id) VALUES (?,?)",
            (wsid, routing_id),
        )


def mo_has_transactions(conn, mo_number):
    c = conn.execute("SELECT COUNT(*) c FROM work_sessions WHERE mo_number=?", (mo_number,)).fetchone()["c"]
    return c > 0