-- 0001 — Kernel baseline (Phase 1).
-- Users (doc 01 §2), role assignments (doc 01 §3), audit events (doc 01 §4).
-- UUID primary keys generated on the creating device (doc 10 §1).
-- branches/organization tables arrive in Phase 2; branch_id foreign keys are added then.

CREATE TABLE users (
    id CHAR(36) NOT NULL PRIMARY KEY,
    full_name_ar VARCHAR(200) NOT NULL,
    full_name_en VARCHAR(200) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(190) NULL,
    pin_hash VARCHAR(255) NULL,
    password_hash VARCHAR(255) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    employee_id CHAR(36) NULL,
    created_at_utc DATETIME NOT NULL,
    updated_at_utc DATETIME NOT NULL,
    CONSTRAINT uq_users_phone UNIQUE (phone),
    CONSTRAINT ck_users_status CHECK (status IN ('active', 'suspended', 'terminated'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE role_assignments (
    id CHAR(36) NOT NULL PRIMARY KEY,
    user_id CHAR(36) NOT NULL,
    role VARCHAR(30) NOT NULL,
    all_branches TINYINT NOT NULL DEFAULT 0,
    branch_id CHAR(36) NULL,
    valid_from DATE NULL,
    valid_to DATE NULL,
    revoked_at_utc DATETIME NULL,
    created_at_utc DATETIME NOT NULL,
    CONSTRAINT fk_role_assignments_user FOREIGN KEY (user_id) REFERENCES users (id),
    CONSTRAINT ck_role_assignments_role CHECK (role IN ('OWNER', 'ACCOUNTANT', 'BRANCH_MANAGER', 'CASHIER', 'BARISTA_KITCHEN', 'STOREKEEPER')),
    CONSTRAINT ck_role_assignments_scope CHECK ((all_branches = 1 AND branch_id IS NULL) OR (all_branches = 0 AND branch_id IS NOT NULL))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX ix_role_assignments_user ON role_assignments (user_id);

CREATE TABLE audit_events (
    id CHAR(36) NOT NULL PRIMARY KEY,
    occurred_at_utc DATETIME NOT NULL,
    occurred_at_local DATETIME NOT NULL,
    business_day DATE NOT NULL,
    org_id CHAR(36) NOT NULL,
    branch_id CHAR(36) NULL,
    device_id VARCHAR(64) NULL,
    shift_id CHAR(36) NULL,
    actor_user_id CHAR(36) NOT NULL,
    approved_by_user_id CHAR(36) NULL,
    entity_type VARCHAR(60) NOT NULL,
    entity_id VARCHAR(64) NOT NULL,
    action VARCHAR(60) NOT NULL,
    before_json LONGTEXT NULL,
    after_json LONGTEXT NULL,
    reason_code VARCHAR(40) NULL,
    note TEXT NULL,
    source VARCHAR(20) NOT NULL,
    CONSTRAINT ck_audit_events_source CHECK (source IN ('pos', 'back_office', 'sync', 'system')),
    CONSTRAINT ck_audit_events_no_self_approval CHECK (approved_by_user_id IS NULL OR approved_by_user_id <> actor_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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);
