textclsy

Sleeping

textclsy / app /schema_sql.py

Upload 62 files

c8f4656 verified 2 months ago

4.89 kB

"""Database schema.
Kept apart from store.py so the SQL is readable in one place.
The `complaints` table started life as a classification log and is now the real
complaint entity. Rather than adding a parallel table, the extra columns are
added by an idempotent migration in store.migrate() -- existing rows (from the
classifier demo page) simply have no user and stay in status 'Submitted'.
"""

BASE_SCHEMA = """
CREATE TABLE IF NOT EXISTS labels (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT    NOT NULL UNIQUE,
    description TEXT    NOT NULL DEFAULT '',
    active      INTEGER NOT NULL DEFAULT 1,
    created_at  TEXT    NOT NULL
);
CREATE TABLE IF NOT EXISTS complaints (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    original_text   TEXT    NOT NULL,
    translated_text TEXT    NOT NULL,
    source_lang     TEXT    NOT NULL DEFAULT 'unknown',
    was_translated  INTEGER NOT NULL DEFAULT 0,
    predicted_label TEXT    NOT NULL,
    confidence      REAL    NOT NULL,
    scores_json     TEXT    NOT NULL DEFAULT '[]',
    engine          TEXT    NOT NULL DEFAULT '',
    took_ms         INTEGER NOT NULL DEFAULT 0,
    created_at      TEXT    NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_complaints_created ON complaints(created_at DESC);
CREATE TABLE IF NOT EXISTS users (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    full_name     TEXT    NOT NULL,
    email         TEXT    NOT NULL UNIQUE,
    phone         TEXT    NOT NULL DEFAULT '',
    password_hash TEXT    NOT NULL,
    language      TEXT    NOT NULL DEFAULT 'en',
    is_admin      INTEGER NOT NULL DEFAULT 0,
    created_at    TEXT    NOT NULL
);
CREATE TABLE IF NOT EXISTS complaint_photos (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    complaint_id INTEGER NOT NULL REFERENCES complaints(id) ON DELETE CASCADE,
    filename     TEXT    NOT NULL,
    mime         TEXT    NOT NULL DEFAULT 'image/jpeg',
    bytes        INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT    NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_photos_complaint ON complaint_photos(complaint_id);
-- One row per status change: this is what the tracking timeline renders.
CREATE TABLE IF NOT EXISTS status_events (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    complaint_id INTEGER NOT NULL REFERENCES complaints(id) ON DELETE CASCADE,
    status       TEXT    NOT NULL,
    note         TEXT    NOT NULL DEFAULT '',
    created_at   TEXT    NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_events_complaint ON status_events(complaint_id, id);
CREATE TABLE IF NOT EXISTS notifications (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id      INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    complaint_id INTEGER REFERENCES complaints(id) ON DELETE CASCADE,
    message      TEXT    NOT NULL,
    is_read      INTEGER NOT NULL DEFAULT 0,
    created_at   TEXT    NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_notif_user ON notifications(user_id, id DESC);
CREATE TABLE IF NOT EXISTS feedback (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    complaint_id INTEGER NOT NULL UNIQUE REFERENCES complaints(id) ON DELETE CASCADE,
    rating       INTEGER NOT NULL,
    comment      TEXT    NOT NULL DEFAULT '',
    created_at   TEXT    NOT NULL
);
CREATE TABLE IF NOT EXISTS departments (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    name       TEXT    NOT NULL UNIQUE,
    created_at TEXT    NOT NULL
);
"""

# (column, DDL type/default) added to `complaints` if not already present.
COMPLAINT_COLUMNS = [
    ("ref", "TEXT"),                                  # UC2026-000123
    ("user_id", "INTEGER"),
    ("category", "TEXT NOT NULL DEFAULT ''"),         # final, admin may override
    ("location", "TEXT NOT NULL DEFAULT ''"),
    ("latitude", "REAL"),
    ("longitude", "REAL"),
    ("status", "TEXT NOT NULL DEFAULT 'Submitted'"),
    ("department", "TEXT NOT NULL DEFAULT ''"),
    ("extra_details", "TEXT NOT NULL DEFAULT ''"),
    ("updated_at", "TEXT"),
]

# The lifecycle a complaint moves through, in order.
STATUSES = ["Submitted", "Under Review", "Assigned", "In Progress", "Resolved"]

DEFAULT_DEPARTMENTS = [
    "Municipal Engineering Department",
    "Water Board",
    "Electricity Board",
    "Public Health Department",
    "Waste Management Unit",
    "Roads & Highways Authority",
    "General Administration",
]

# Which department a category routes to by default when an admin assigns.
CATEGORY_DEPARTMENT = {
    "Water Supply": "Water Board",
    "Electricity": "Electricity Board",
    "Street Lighting": "Electricity Board",
    "Road & Transport": "Roads & Highways Authority",
    "Waste & Sanitation": "Waste Management Unit",
    "Public Health": "Public Health Department",
    "Noise & Pollution": "Public Health Department",
    "Building & Property": "Municipal Engineering Department",
}

	"""Database schema.

	Kept apart from store.py so the SQL is readable in one place.

	The `complaints` table started life as a classification log and is now the real
	complaint entity. Rather than adding a parallel table, the extra columns are
	added by an idempotent migration in store.migrate() -- existing rows (from the
	classifier demo page) simply have no user and stay in status 'Submitted'.
	"""

	BASE_SCHEMA = """
	CREATE TABLE IF NOT EXISTS labels (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL UNIQUE,
	description TEXT NOT NULL DEFAULT '',
	active INTEGER NOT NULL DEFAULT 1,
	created_at TEXT NOT NULL
	);

	CREATE TABLE IF NOT EXISTS complaints (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	original_text TEXT NOT NULL,
	translated_text TEXT NOT NULL,
	source_lang TEXT NOT NULL DEFAULT 'unknown',
	was_translated INTEGER NOT NULL DEFAULT 0,
	predicted_label TEXT NOT NULL,
	confidence REAL NOT NULL,
	scores_json TEXT NOT NULL DEFAULT '[]',
	engine TEXT NOT NULL DEFAULT '',
	took_ms INTEGER NOT NULL DEFAULT 0,
	created_at TEXT NOT NULL
	);

	CREATE INDEX IF NOT EXISTS idx_complaints_created ON complaints(created_at DESC);

	CREATE TABLE IF NOT EXISTS users (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	full_name TEXT NOT NULL,
	email TEXT NOT NULL UNIQUE,
	phone TEXT NOT NULL DEFAULT '',
	password_hash TEXT NOT NULL,
	language TEXT NOT NULL DEFAULT 'en',
	is_admin INTEGER NOT NULL DEFAULT 0,
	created_at TEXT NOT NULL
	);

	CREATE TABLE IF NOT EXISTS complaint_photos (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	complaint_id INTEGER NOT NULL REFERENCES complaints(id) ON DELETE CASCADE,
	filename TEXT NOT NULL,
	mime TEXT NOT NULL DEFAULT 'image/jpeg',
	bytes INTEGER NOT NULL DEFAULT 0,
	created_at TEXT NOT NULL
	);

	CREATE INDEX IF NOT EXISTS idx_photos_complaint ON complaint_photos(complaint_id);

	-- One row per status change: this is what the tracking timeline renders.
	CREATE TABLE IF NOT EXISTS status_events (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	complaint_id INTEGER NOT NULL REFERENCES complaints(id) ON DELETE CASCADE,
	status TEXT NOT NULL,
	note TEXT NOT NULL DEFAULT '',
	created_at TEXT NOT NULL
	);

	CREATE INDEX IF NOT EXISTS idx_events_complaint ON status_events(complaint_id, id);

	CREATE TABLE IF NOT EXISTS notifications (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	complaint_id INTEGER REFERENCES complaints(id) ON DELETE CASCADE,
	message TEXT NOT NULL,
	is_read INTEGER NOT NULL DEFAULT 0,
	created_at TEXT NOT NULL
	);

	CREATE INDEX IF NOT EXISTS idx_notif_user ON notifications(user_id, id DESC);

	CREATE TABLE IF NOT EXISTS feedback (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	complaint_id INTEGER NOT NULL UNIQUE REFERENCES complaints(id) ON DELETE CASCADE,
	rating INTEGER NOT NULL,
	comment TEXT NOT NULL DEFAULT '',
	created_at TEXT NOT NULL
	);

	CREATE TABLE IF NOT EXISTS departments (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL UNIQUE,
	created_at TEXT NOT NULL
	);
	"""

	# (column, DDL type/default) added to `complaints` if not already present.
	COMPLAINT_COLUMNS = [
	("ref", "TEXT"), # UC2026-000123
	("user_id", "INTEGER"),
	("category", "TEXT NOT NULL DEFAULT ''"), # final, admin may override
	("location", "TEXT NOT NULL DEFAULT ''"),
	("latitude", "REAL"),
	("longitude", "REAL"),
	("status", "TEXT NOT NULL DEFAULT 'Submitted'"),
	("department", "TEXT NOT NULL DEFAULT ''"),
	("extra_details", "TEXT NOT NULL DEFAULT ''"),
	("updated_at", "TEXT"),
	]

	# The lifecycle a complaint moves through, in order.
	STATUSES = ["Submitted", "Under Review", "Assigned", "In Progress", "Resolved"]

	DEFAULT_DEPARTMENTS = [
	"Municipal Engineering Department",
	"Water Board",
	"Electricity Board",
	"Public Health Department",
	"Waste Management Unit",
	"Roads & Highways Authority",
	"General Administration",
	]

	# Which department a category routes to by default when an admin assigns.
	CATEGORY_DEPARTMENT = {
	"Water Supply": "Water Board",
	"Electricity": "Electricity Board",
	"Street Lighting": "Electricity Board",
	"Road & Transport": "Roads & Highways Authority",
	"Waste & Sanitation": "Waste Management Unit",
	"Public Health": "Public Health Department",
	"Noise & Pollution": "Public Health Department",
	"Building & Property": "Municipal Engineering Department",
	}