CREATE TABLE installation_jobs (
id INTEGER PRIMARY KEY AUTOINCREMENT,
job_number VARCHAR(50) UNIQUE NOT NULL,
customer_name VARCHAR(255) NOT NULL,
customer_phone VARCHAR(20) NOT NULL,
customer_address TEXT NOT NULL,
package_id INTEGER NOT NULL,
installation_date DATE NOT NULL,
installation_time VARCHAR(20),
assigned_technician_id INTEGER,
status VARCHAR(50) DEFAULT 'scheduled',
priority VARCHAR(20) DEFAULT 'normal',
notes TEXT,
equipment_needed TEXT,
estimated_duration INTEGER DEFAULT 120,
created_by_admin_id INTEGER,
completed_at DATETIME,
completion_notes TEXT,
customer_latitude DECIMAL(10, 8),
customer_longitude DECIMAL(11, 8),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (package_id) REFERENCES packages(id),
FOREIGN KEY (assigned_technician_id) REFERENCES technicians(id)
)