src/db/schema.py

import sqlite3

SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS meta (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT NOT NULL UNIQUE,
  role TEXT NOT NULL CHECK(role IN ('staff','admin')),
  password_hash TEXT NOT NULL,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at_utc TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS rooms (
  room_no INTEGER PRIMARY KEY,
  room_type TEXT NOT NULL,
  sellable INTEGER NOT NULL DEFAULT 1,
  status TEXT NOT NULL CHECK(status IN ('free','occupied','need_cleaning','out_of_service')),
  created_at_utc TEXT NOT NULL,
  updated_at_utc TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS room_problems (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  room_no INTEGER NOT NULL,
  problem_type TEXT NOT NULL CHECK(problem_type IN ('heater','ac','bed','other')),
  description TEXT,
  is_active INTEGER NOT NULL DEFAULT 1,
  created_at_utc TEXT NOT NULL,
  updated_at_utc TEXT NOT NULL,
  FOREIGN KEY(room_no) REFERENCES rooms(room_no)
);

-- Immutable history: room status changes
CREATE TABLE IF NOT EXISTS room_status_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  room_no INTEGER NOT NULL,
  from_status TEXT,
  to_status TEXT NOT NULL,
  note TEXT NOT NULL,
  changed_by TEXT NOT NULL,
  changed_at_utc TEXT NOT NULL,
  FOREIGN KEY(room_no) REFERENCES rooms(room_no)
);

-- Immutable history: problem changes
CREATE TABLE IF NOT EXISTS room_problem_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  room_no INTEGER NOT NULL,
  problem_type TEXT NOT NULL,
  action TEXT NOT NULL CHECK(action IN ('add','deactivate','update')),
  description TEXT,
  note TEXT NOT NULL,
  changed_by TEXT NOT NULL,
  changed_at_utc TEXT NOT NULL,
  FOREIGN KEY(room_no) REFERENCES rooms(room_no)
);

CREATE TABLE IF NOT EXISTS reservations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  room_no INTEGER NOT NULL,
  guest_name TEXT NOT NULL,
  guest_phone TEXT NOT NULL,
  check_in_date TEXT NOT NULL,   -- YYYY-MM-DD local
  check_out_date TEXT NOT NULL,  -- YYYY-MM-DD local
  nights INTEGER NOT NULL,
  rate_ro REAL NOT NULL,
  total_ro REAL NOT NULL,
  status TEXT NOT NULL CHECK(status IN ('active','closed','cancelled')),
  created_by TEXT NOT NULL,
  created_at_utc TEXT NOT NULL,
  updated_by TEXT NOT NULL,
  updated_at_utc TEXT NOT NULL,
  close_note TEXT,
  FOREIGN KEY(room_no) REFERENCES rooms(room_no)
);

CREATE TABLE IF NOT EXISTS reservation_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  reservation_id INTEGER NOT NULL,
  action TEXT NOT NULL CHECK(action IN ('create','update','close','cancel')),
  note TEXT NOT NULL,
  changed_by TEXT NOT NULL,
  changed_at_utc TEXT NOT NULL,
  snapshot_json TEXT,
  FOREIGN KEY(reservation_id) REFERENCES reservations(id)
);

CREATE TABLE IF NOT EXISTS payments (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  reservation_id INTEGER NOT NULL,
  amount_ro REAL NOT NULL,
  method TEXT NOT NULL CHECK(method IN ('cash','card','bank','other')),
  note TEXT,
  changed_by TEXT NOT NULL,
  changed_at_utc TEXT NOT NULL,
  FOREIGN KEY(reservation_id) REFERENCES reservations(id)
);

CREATE TABLE IF NOT EXISTS payment_history (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  payment_id INTEGER,
  reservation_id INTEGER NOT NULL,
  action TEXT NOT NULL CHECK(action IN ('add','void')),
  note TEXT NOT NULL,
  changed_by TEXT NOT NULL,
  changed_at_utc TEXT NOT NULL,
  FOREIGN KEY(reservation_id) REFERENCES reservations(id)
);

CREATE TABLE IF NOT EXISTS income_expense (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  entry_date TEXT NOT NULL, -- YYYY-MM-DD local (business day)
  kind TEXT NOT NULL CHECK(kind IN ('income','expense')),
  category TEXT NOT NULL,
  amount_ro REAL NOT NULL,
  method TEXT,     -- income
  vendor TEXT,     -- expense
  note TEXT,
  changed_by TEXT NOT NULL,
  changed_at_utc TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS day_closings (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  business_date TEXT NOT NULL UNIQUE, -- YYYY-MM-DD closed day
  closed_at_utc TEXT NOT NULL,
  closed_by TEXT NOT NULL,
  note TEXT NOT NULL,
  pdf_path TEXT,
  pdf_bytes BLOB
);

CREATE TABLE IF NOT EXISTS audit_log (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  entity TEXT NOT NULL,
  entity_id TEXT,
  action TEXT NOT NULL,
  note TEXT NOT NULL,
  changed_by TEXT NOT NULL,
  changed_at_utc TEXT NOT NULL
);
"""

def init_schema(conn: sqlite3.Connection) -> None:
    conn.executescript(SCHEMA_SQL)
    conn.execute("INSERT OR IGNORE INTO meta(key,value) VALUES(?,?)", ("app", "venus-hotel-frontdesk"))
    conn.execute("INSERT OR IGNORE INTO meta(key,value) VALUES(?,?)", ("schema_version", "1"))
    conn.execute("INSERT OR IGNORE INTO meta(key,value) VALUES(?,?)", ("timezone", "Asia/Muscat"))