PRAGMA foreign_keys = ON;
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,email TEXT NOT NULL UNIQUE,password_hash TEXT NOT NULL,role TEXT NOT NULL DEFAULT 'worker',is_active INTEGER NOT NULL DEFAULT 1,created_at TEXT DEFAULT CURRENT_TIMESTAMP,updated_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS companies (id INTEGER PRIMARY KEY AUTOINCREMENT,code TEXT NOT NULL UNIQUE,name TEXT NOT NULL,cr_number TEXT,vat_number TEXT,status TEXT NOT NULL DEFAULT 'active',created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS branches (id INTEGER PRIMARY KEY AUTOINCREMENT,company_id INTEGER NOT NULL REFERENCES companies(id),code TEXT NOT NULL UNIQUE,name TEXT NOT NULL,city TEXT);
CREATE TABLE IF NOT EXISTS projects (id INTEGER PRIMARY KEY AUTOINCREMENT,company_id INTEGER NOT NULL REFERENCES companies(id),branch_id INTEGER REFERENCES branches(id),code TEXT NOT NULL UNIQUE,name TEXT NOT NULL,client_name TEXT,contract_value REAL NOT NULL DEFAULT 0,spent_amount REAL NOT NULL DEFAULT 0,progress REAL NOT NULL DEFAULT 0,status TEXT NOT NULL DEFAULT 'active',starts_on TEXT,ends_on TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS employees (id INTEGER PRIMARY KEY AUTOINCREMENT,company_id INTEGER NOT NULL REFERENCES companies(id),branch_id INTEGER REFERENCES branches(id),project_id INTEGER REFERENCES projects(id),emp_code TEXT NOT NULL UNIQUE,name TEXT NOT NULL,job_title TEXT,base_salary REAL NOT NULL DEFAULT 0,iqama_expiry TEXT,status TEXT NOT NULL DEFAULT 'active',created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS equipment (id INTEGER PRIMARY KEY AUTOINCREMENT,code TEXT NOT NULL UNIQUE,project_id INTEGER REFERENCES projects(id),name TEXT NOT NULL,ownership_type TEXT NOT NULL DEFAULT 'owned',meter_hours REAL NOT NULL DEFAULT 0,hourly_cost REAL NOT NULL DEFAULT 0,status TEXT NOT NULL DEFAULT 'working',created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS inventory_items (id INTEGER PRIMARY KEY AUTOINCREMENT,code TEXT NOT NULL UNIQUE,name TEXT NOT NULL,unit TEXT NOT NULL,on_hand REAL NOT NULL DEFAULT 0,reserved_qty REAL NOT NULL DEFAULT 0,reorder_level REAL NOT NULL DEFAULT 0,status TEXT NOT NULL DEFAULT 'available',created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS suppliers (id INTEGER PRIMARY KEY AUTOINCREMENT,code TEXT NOT NULL UNIQUE,name TEXT NOT NULL,category TEXT,vat_number TEXT,rating REAL NOT NULL DEFAULT 0,status TEXT NOT NULL DEFAULT 'review',created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS approval_requests (id INTEGER PRIMARY KEY AUTOINCREMENT,requester_id INTEGER NOT NULL REFERENCES users(id),request_type TEXT NOT NULL,reference_code TEXT,amount REAL,current_role TEXT NOT NULL,status TEXT NOT NULL DEFAULT 'pending',payload TEXT,approved_by INTEGER REFERENCES users(id),approved_at TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS journal_entries (id INTEGER PRIMARY KEY AUTOINCREMENT,entry_code TEXT NOT NULL UNIQUE,project_id INTEGER REFERENCES projects(id),entry_date TEXT NOT NULL,description TEXT NOT NULL,status TEXT NOT NULL DEFAULT 'draft',posted_by INTEGER REFERENCES users(id),posted_at TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS journal_entry_lines (id INTEGER PRIMARY KEY AUTOINCREMENT,journal_entry_id INTEGER NOT NULL REFERENCES journal_entries(id) ON DELETE CASCADE,account_code TEXT NOT NULL,debit REAL NOT NULL DEFAULT 0,credit REAL NOT NULL DEFAULT 0,cost_center TEXT);
CREATE TABLE IF NOT EXISTS app_state (state_key TEXT PRIMARY KEY,state_json TEXT NOT NULL,version INTEGER NOT NULL DEFAULT 1,updated_by INTEGER NOT NULL REFERENCES users(id),updated_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS app_state_revisions (id INTEGER PRIMARY KEY AUTOINCREMENT,state_key TEXT NOT NULL,version INTEGER NOT NULL,changed_keys TEXT NOT NULL,changed_by INTEGER NOT NULL REFERENCES users(id),created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,UNIQUE(state_key,version));
CREATE INDEX IF NOT EXISTS idx_state_revision_created ON app_state_revisions(created_at);
CREATE TABLE IF NOT EXISTS documents (id INTEGER PRIMARY KEY AUTOINCREMENT,uploaded_by INTEGER NOT NULL REFERENCES users(id),original_name TEXT NOT NULL,stored_name TEXT NOT NULL UNIQUE,mime_type TEXT NOT NULL,file_size INTEGER NOT NULL,category TEXT,project_code TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS audit_logs (id INTEGER PRIMARY KEY AUTOINCREMENT,user_id INTEGER REFERENCES users(id),action TEXT NOT NULL,entity TEXT NOT NULL,entity_id TEXT,metadata TEXT,ip_address TEXT,created_at TEXT DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS schema_migrations (version TEXT PRIMARY KEY,applied_at TEXT DEFAULT CURRENT_TIMESTAMP);
INSERT OR IGNORE INTO schema_migrations(version) VALUES ('2026_07_20_001_initial');