Spaces:
Runtime error
Runtime error
File size: 4,750 Bytes
9d9d2a1 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 |
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"))
|