CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY,name TEXT NOT NULL,email TEXT UNIQUE NOT NULL,password_hash TEXT NOT NULL,role TEXT NOT NULL CHECK(role IN ('admin','tecnico')),active BOOLEAN DEFAULT TRUE,created_at TIMESTAMPTZ DEFAULT now());
CREATE TABLE IF NOT EXISTS clients (id SERIAL PRIMARY KEY,name TEXT NOT NULL,rut TEXT,phone TEXT,email TEXT,address TEXT,contact TEXT,created_at TIMESTAMPTZ DEFAULT now());
CREATE TABLE IF NOT EXISTS equipment (id SERIAL PRIMARY KEY,client_id INT REFERENCES clients(id) ON DELETE CASCADE,type TEXT,brand TEXT,model TEXT,serial_number TEXT,capacity_btu INT,refrigerant TEXT,location TEXT,qr_code TEXT UNIQUE,created_at TIMESTAMPTZ DEFAULT now());
CREATE TABLE IF NOT EXISTS maintenance (id SERIAL PRIMARY KEY,folio TEXT UNIQUE NOT NULL,client_id INT REFERENCES clients(id),equipment_id INT REFERENCES equipment(id),technician_id INT REFERENCES users(id),service_date DATE NOT NULL DEFAULT CURRENT_DATE,start_time TIME,end_time TIME,maintenance_type TEXT,diagnosis TEXT,work_done TEXT,materials TEXT,recommendations TEXT,result TEXT,next_service DATE,tasks JSONB DEFAULT '[]',measurements JSONB DEFAULT '{}',customer_name TEXT,customer_rut TEXT,technician_signature TEXT,customer_signature TEXT,created_at TIMESTAMPTZ DEFAULT now());
CREATE TABLE IF NOT EXISTS photos (id SERIAL PRIMARY KEY,maintenance_id INT REFERENCES maintenance(id) ON DELETE CASCADE,stage TEXT CHECK(stage IN ('antes','durante','despues')),file_path TEXT NOT NULL,created_at TIMESTAMPTZ DEFAULT now());
CREATE INDEX IF NOT EXISTS idx_equipment_client ON equipment(client_id); CREATE INDEX IF NOT EXISTS idx_maintenance_client ON maintenance(client_id); CREATE INDEX IF NOT EXISTS idx_maintenance_equipment ON maintenance(equipment_id); CREATE INDEX IF NOT EXISTS idx_maintenance_next ON maintenance(next_service);
CREATE SEQUENCE IF NOT EXISTS maintenance_folio_seq START 1;