48 lines
1.7 KiB
SQL
Executable File
48 lines
1.7 KiB
SQL
Executable File
CREATE TABLE IF NOT EXISTS employees (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL,
|
|
department TEXT,
|
|
supervisor TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS violations (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
employee_id INTEGER NOT NULL REFERENCES employees(id),
|
|
violation_type TEXT NOT NULL,
|
|
violation_name TEXT NOT NULL,
|
|
category TEXT NOT NULL DEFAULT 'General',
|
|
points INTEGER NOT NULL,
|
|
incident_date TEXT NOT NULL,
|
|
incident_time TEXT,
|
|
location TEXT,
|
|
details TEXT,
|
|
submitted_by TEXT,
|
|
witness_name TEXT,
|
|
negated INTEGER NOT NULL DEFAULT 0,
|
|
negated_at DATETIME,
|
|
prior_active_points INTEGER, -- snapshot at time of logging
|
|
prior_tier_label TEXT, -- optional human-readable tier
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS violation_resolutions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
violation_id INTEGER NOT NULL REFERENCES violations(id) ON DELETE CASCADE,
|
|
resolution_type TEXT NOT NULL,
|
|
details TEXT,
|
|
resolved_by TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Active score: only non-negated violations in rolling 90 days
|
|
CREATE VIEW IF NOT EXISTS active_cpas_scores AS
|
|
SELECT
|
|
employee_id,
|
|
SUM(points) AS active_points,
|
|
COUNT(*) AS violation_count
|
|
FROM violations
|
|
WHERE negated = 0
|
|
AND incident_date >= DATE('now', '-90 days')
|
|
GROUP BY employee_id;
|