CREATE TABLE IF NOT EXISTS companies (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,code VARCHAR(30) NOT NULL UNIQUE,address TEXT NULL,phone VARCHAR(50) NULL,email VARCHAR(150) NULL,pan_vat VARCHAR(80) NULL,logo VARCHAR(255) NULL,qr_image VARCHAR(255) NULL,created_at DATETIME NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS branches (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,name VARCHAR(150) NOT NULL,address TEXT NULL,phone VARCHAR(50) NULL,created_at DATETIME NOT NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS roles (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(80) NOT NULL UNIQUE,description VARCHAR(255) NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS permissions (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,code VARCHAR(100) NOT NULL UNIQUE,label VARCHAR(150) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS role_permissions (role_id INT UNSIGNED NOT NULL,permission_id INT UNSIGNED NOT NULL,PRIMARY KEY(role_id,permission_id),FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS users (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,branch_id INT UNSIGNED NULL,name VARCHAR(150) NOT NULL,email VARCHAR(150) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role VARCHAR(80) NOT NULL DEFAULT 'technician',active TINYINT(1) NOT NULL DEFAULT 1,created_at DATETIME NOT NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,FOREIGN KEY(branch_id) REFERENCES branches(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS customers (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,name VARCHAR(150) NOT NULL,phone VARCHAR(50) NOT NULL,email VARCHAR(150) NULL,address TEXT NULL,pan_vat VARCHAR(80) NULL,portal_token VARCHAR(64) NULL UNIQUE,created_at DATETIME NOT NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS devices (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_id INT UNSIGNED NOT NULL,device_type VARCHAR(100) NOT NULL,brand VARCHAR(100) NULL,model VARCHAR(100) NULL,serial_no VARCHAR(150) NULL,accessories TEXT NULL,physical_condition TEXT NULL,warranty_expiry DATE NULL,created_at DATETIME NOT NULL,FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,INDEX(serial_no)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tickets (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,branch_id INT UNSIGNED NULL,customer_id INT UNSIGNED NOT NULL,device_id INT UNSIGNED NOT NULL,ticket_no VARCHAR(80) NOT NULL UNIQUE,rma_no VARCHAR(80) NOT NULL UNIQUE,priority VARCHAR(30) NOT NULL DEFAULT 'Normal',status VARCHAR(60) NOT NULL DEFAULT 'Received',assigned_to INT UNSIGNED NULL,issue TEXT NOT NULL,diagnosis TEXT NULL,repair_notes TEXT NULL,estimate DECIMAL(12,2) NOT NULL DEFAULT 0,expected_date DATE NULL,closed_at DATETIME NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE,FOREIGN KEY(branch_id) REFERENCES branches(id) ON DELETE SET NULL,FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,FOREIGN KEY(device_id) REFERENCES devices(id) ON DELETE CASCADE,FOREIGN KEY(assigned_to) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS ticket_status_history (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ticket_id INT UNSIGNED NOT NULL,status VARCHAR(60) NOT NULL,note TEXT NULL,changed_by INT UNSIGNED NULL,created_at DATETIME NOT NULL,FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS ticket_notes (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ticket_id INT UNSIGNED NOT NULL,user_id INT UNSIGNED NULL,note_type VARCHAR(20) NOT NULL DEFAULT 'internal',note TEXT NOT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS ticket_files (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ticket_id INT UNSIGNED NOT NULL,user_id INT UNSIGNED NULL,file_name VARCHAR(255) NOT NULL,stored_name VARCHAR(255) NOT NULL,mime VARCHAR(120) NULL,size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,created_at DATETIME NOT NULL,FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS ticket_signatures (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ticket_id INT UNSIGNED NOT NULL,type VARCHAR(20) NOT NULL,signature_data LONGTEXT NOT NULL,signed_by VARCHAR(150) NULL,created_at DATETIME NOT NULL,FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS vendors (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,name VARCHAR(150) NOT NULL,contact_person VARCHAR(150) NULL,phone VARCHAR(50) NULL,email VARCHAR(150) NULL,address TEXT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS vendor_claims (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ticket_id INT UNSIGNED NOT NULL,vendor_id INT UNSIGNED NOT NULL,claim_no VARCHAR(100) NULL,dispatch_date DATE NULL,expected_date DATE NULL,status VARCHAR(60) NOT NULL DEFAULT 'Sent to Vendor',vendor_decision VARCHAR(80) NULL,replacement_serial VARCHAR(150) NULL,courier VARCHAR(100) NULL,tracking_no VARCHAR(150) NULL,notes TEXT NULL,received_date DATE NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,FOREIGN KEY(vendor_id) REFERENCES vendors(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS vendor_claim_history (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,claim_id INT UNSIGNED NOT NULL,status VARCHAR(60) NOT NULL,note TEXT NULL,changed_by INT UNSIGNED NULL,created_at DATETIME NOT NULL,FOREIGN KEY(claim_id) REFERENCES vendor_claims(id) ON DELETE CASCADE,FOREIGN KEY(changed_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS repair_partners (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,name VARCHAR(150) NOT NULL,contact_person VARCHAR(150) NULL,phone VARCHAR(50) NULL,email VARCHAR(150) NULL,address TEXT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS external_repairs (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ticket_id INT UNSIGNED NOT NULL,partner_id INT UNSIGNED NOT NULL,sent_date DATE NULL,expected_date DATE NULL,received_date DATE NULL,status VARCHAR(60) NOT NULL DEFAULT 'Sent to Repair Shop',quoted_cost DECIMAL(12,2) NOT NULL DEFAULT 0,actual_cost DECIMAL(12,2) NOT NULL DEFAULT 0,tracking_no VARCHAR(150) NULL,courier VARCHAR(100) NULL,notes TEXT NULL,created_at DATETIME NOT NULL,updated_at DATETIME NOT NULL,FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,FOREIGN KEY(partner_id) REFERENCES repair_partners(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS products (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,sku VARCHAR(80) NULL UNIQUE,name VARCHAR(150) NOT NULL,category VARCHAR(100) NULL,brand VARCHAR(100) NULL,qty DECIMAL(12,2) NOT NULL DEFAULT 0,reorder_level DECIMAL(12,2) NOT NULL DEFAULT 0,cost_price DECIMAL(12,2) NOT NULL DEFAULT 0,sale_price DECIMAL(12,2) NOT NULL DEFAULT 0,created_at DATETIME NOT NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS product_serials (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id INT UNSIGNED NOT NULL,serial_no VARCHAR(150) NOT NULL UNIQUE,status VARCHAR(40) NOT NULL DEFAULT 'In Stock',location VARCHAR(120) NULL,ticket_id INT UNSIGNED NULL,created_at DATETIME NOT NULL,FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS stock_movements (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_id INT UNSIGNED NOT NULL,type VARCHAR(30) NOT NULL,qty DECIMAL(12,2) NOT NULL,reference_type VARCHAR(80) NULL,reference_id BIGINT UNSIGNED NULL,note TEXT NULL,user_id INT UNSIGNED NULL,created_at DATETIME NOT NULL,FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,ticket_id INT UNSIGNED NOT NULL,method VARCHAR(50) NOT NULL,amount DECIMAL(12,2) NOT NULL,reference_no VARCHAR(150) NULL,screenshot VARCHAR(255) NULL,status VARCHAR(30) NOT NULL DEFAULT 'Pending',verified_by INT UNSIGNED NULL,paid_at DATETIME NULL,created_at DATETIME NOT NULL,FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,FOREIGN KEY(verified_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS message_templates (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,channel VARCHAR(20) NOT NULL,name VARCHAR(100) NOT NULL,body TEXT NOT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS communication_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,channel VARCHAR(20) NOT NULL,recipient VARCHAR(150) NOT NULL,message TEXT NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'Queued',provider_response TEXT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS settings (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id INT UNSIGNED NOT NULL,setting_key VARCHAR(120) NOT NULL,setting_value TEXT NULL,UNIQUE KEY uk_setting(company_id,setting_key),FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NULL,company_id INT UNSIGNED NULL,action VARCHAR(100) NOT NULL,entity_type VARCHAR(80) NULL,entity_id BIGINT UNSIGNED NULL,ip_address VARCHAR(45) NULL,details TEXT NULL,created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,FOREIGN KEY(company_id) REFERENCES companies(id) ON DELETE SET NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS password_resets (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id INT UNSIGNED NOT NULL,token_hash CHAR(64) NOT NULL UNIQUE,expires_at DATETIME NOT NULL,used_at DATETIME NULL,requested_ip VARCHAR(45) NULL,created_at DATETIME NOT NULL,FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,INDEX(expires_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;