-- 0001 — Kernel baseline (Phase 1), branch database (SQLite).
-- Same logical schema as migrations/central/0001 — users pull down to branches as
-- master data (doc 10 §4) so PIN login works offline.

CREATE TABLE users (
    id TEXT NOT NULL PRIMARY KEY,
    full_name_ar TEXT NOT NULL,
    full_name_en TEXT NOT NULL,
    phone TEXT NOT NULL UNIQUE,
    email TEXT NULL,
    pin_hash TEXT NULL,
    password_hash TEXT NULL,
    status TEXT NOT NULL DEFAULT 'active'
        CHECK (status IN ('active', 'suspended', 'terminated')),
    employee_id TEXT NULL,
    created_at_utc TEXT NOT NULL,
    updated_at_utc TEXT NOT NULL
);

CREATE TABLE role_assignments (
    id TEXT NOT NULL PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users (id),
    role TEXT NOT NULL
        CHECK (role IN ('OWNER', 'ACCOUNTANT', 'BRANCH_MANAGER', 'CASHIER', 'BARISTA_KITCHEN', 'STOREKEEPER')),
    all_branches INTEGER NOT NULL DEFAULT 0,
    branch_id TEXT NULL,
    valid_from TEXT NULL,
    valid_to TEXT NULL,
    revoked_at_utc TEXT NULL,
    created_at_utc TEXT NOT NULL,
    CHECK ((all_branches = 1 AND branch_id IS NULL) OR (all_branches = 0 AND branch_id IS NOT NULL))
);

CREATE INDEX ix_role_assignments_user ON role_assignments (user_id);

CREATE TABLE audit_events (
    id TEXT NOT NULL PRIMARY KEY,
    occurred_at_utc TEXT NOT NULL,
    occurred_at_local TEXT NOT NULL,
    business_day TEXT NOT NULL,
    org_id TEXT NOT NULL,
    branch_id TEXT NULL,
    device_id TEXT NULL,
    shift_id TEXT NULL,
    actor_user_id TEXT NOT NULL,
    approved_by_user_id TEXT NULL,
    entity_type TEXT NOT NULL,
    entity_id TEXT NOT NULL,
    action TEXT NOT NULL,
    before_json TEXT NULL,
    after_json TEXT NULL,
    reason_code TEXT NULL,
    note TEXT NULL,
    source TEXT NOT NULL CHECK (source IN ('pos', 'back_office', 'sync', 'system')),
    CHECK (approved_by_user_id IS NULL OR approved_by_user_id <> actor_user_id)
);

CREATE INDEX ix_audit_events_entity ON audit_events (entity_type, entity_id);
CREATE INDEX ix_audit_events_day ON audit_events (business_day);
CREATE INDEX ix_audit_events_actor ON audit_events (actor_user_id);
