#!/usr/bin/env python3
"""Create dated employee hourly-rate history without changing worker records.

Run: python3 scripts/pb-add-employee-rates.py
Credentials are loaded by pb_env from .env.local or the environment.
No rates are seeded: the previous/new payroll list must supply actual values.
Existing compatible collections are verified and left untouched.
"""
import sys

from pb_env import http, superuser_token


def definition(employees):
    return {
        "name": "EMPLOYEE_RATES",
        "type": "base",
        "fields": [
            {"name": "employee", "type": "relation", "required": True,
             "collectionId": employees["id"], "maxSelect": 1,
             "cascadeDelete": False},
            {"name": "effective_from", "type": "date", "required": True},
            {"name": "rate_per_hour", "type": "number", "required": True,
             "min": 0},
            {"name": "notes", "type": "text", "required": False},
            {"name": "created", "type": "autodate", "onCreate": True,
             "onUpdate": False},
            {"name": "updated", "type": "autodate", "onCreate": True,
             "onUpdate": True},
        ],
        "indexes": [
            "CREATE UNIQUE INDEX `idx_employee_rates_employee_day` "
            "ON `EMPLOYEE_RATES` (`employee`, substr(`effective_from`, 1, 10))",
            "CREATE INDEX `idx_employee_rates_employee_date` "
            "ON `EMPLOYEE_RATES` (`employee`, `effective_from`)",
        ],
        # Inherit employee read visibility; all rate writes require superuser.
        "listRule": employees.get("listRule"),
        "viewRule": employees.get("viewRule"),
        "createRule": None,
        "updateRule": None,
        "deleteRule": None,
    }


def verify(actual, expected):
    fields = {f["name"]: f for f in actual.get("fields", [])}
    for wanted in expected["fields"]:
        got = fields.get(wanted["name"], {})
        for key, value in wanted.items():
            if got.get(key) != value:
                raise RuntimeError(f"Incompatible field: {wanted['name']}.{key}")
    for index in expected["indexes"]:
        if index not in actual.get("indexes", []):
            raise RuntimeError("Missing expected rate-history index")
    for rule in ("listRule", "viewRule", "createRule", "updateRule", "deleteRule"):
        if actual.get(rule) != expected[rule]:
            raise RuntimeError(f"Incompatible access rule: {rule}")


def main():
    token = superuser_token()
    code, employees = http("GET", "/api/collections/employees", token=token)
    if code != 200:
        raise RuntimeError(f"Cannot read employees schema: HTTP {code}")
    expected = definition(employees)
    code, existing = http("GET", "/api/collections/employee_rates", token=token)
    if code == 404:
        code, created = http("POST", "/api/collections", expected, token)
        if code != 200:
            raise RuntimeError(f"Create failed: HTTP {code}: {created}")
        print(f"Created EMPLOYEE_RATES: {created['id']}")
    elif code == 200:
        verify(existing, expected)
        print("EMPLOYEE_RATES already exists; no changes made")
    else:
        raise RuntimeError(f"Cannot inspect employee_rates: HTTP {code}")

    code, actual = http("GET", "/api/collections/employee_rates", token=token)
    if code != 200:
        raise RuntimeError(f"Cannot verify new schema: HTTP {code}")
    verify(actual, expected)
    print("Verified fields, indexes and access rules. No employee or rate records changed.")


if __name__ == "__main__":
    try:
        main()
    except Exception as exc:
        sys.exit(str(exc))