-- Migration: UI expansions (invoices org fields, attachments metadata, vehicles fields, lookup seeds)
-- Apply once per environment.

ALTER TABLE invoices
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN sales_org VARCHAR(100) NULL,
  ADD COLUMN distribution_channel VARCHAR(100) NULL,
  ADD COLUMN division VARCHAR(100) NULL,
  ADD COLUMN branch_location VARCHAR(100) NULL;

ALTER TABLE attachments
  ADD COLUMN file_display_name VARCHAR(255) NULL,
  ADD COLUMN file_description TEXT NULL,
  ADD COLUMN file_category VARCHAR(50) NULL,
  ADD COLUMN document_type VARCHAR(50) NULL,
  ADD COLUMN reference_module VARCHAR(50) NULL,
  ADD COLUMN reference_record_id BIGINT UNSIGNED NULL,
  ADD COLUMN file_extension VARCHAR(20) NULL,
  ADD COLUMN file_hash CHAR(64) NULL,
  ADD COLUMN access_level VARCHAR(30) NULL,
  ADD COLUMN confidentiality_level VARCHAR(30) NULL,
  ADD COLUMN data_classification VARCHAR(50) NULL,
  ADD COLUMN contains_pii TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN expiry_date DATE NULL,
  ADD COLUMN tags TEXT NULL;

ALTER TABLE vehicles
  ADD COLUMN vehicle_code VARCHAR(50) NULL,
  ADD COLUMN plate_type VARCHAR(30) NULL,
  ADD COLUMN registration_country VARCHAR(100) NULL,
  ADD COLUMN vin VARCHAR(100) NULL,
  ADD COLUMN vehicle_type VARCHAR(50) NULL,
  ADD COLUMN make VARCHAR(100) NULL,
  ADD COLUMN body_type VARCHAR(50) NULL,
  ADD COLUMN color VARCHAR(50) NULL,
  ADD COLUMN fuel_type VARCHAR(30) NULL,
  ADD COLUMN transmission_type VARCHAR(30) NULL,
  ADD COLUMN engine_capacity VARCHAR(50) NULL,
  ADD COLUMN seating_capacity INT NULL,
  ADD COLUMN load_capacity DECIMAL(18,2) NULL,
  ADD COLUMN ownership_type VARCHAR(30) NULL,
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN assignment_start_date DATE NULL,
  ADD COLUMN assignment_end_date DATE NULL,
  ADD COLUMN purchase_date DATE NULL,
  ADD COLUMN in_service_date DATE NULL,
  ADD COLUMN odometer_reading INT NULL,
  ADD COLUMN odometer_unit VARCHAR(10) NULL;

-- Lookup values (insert for first company)
SET @company_id = (SELECT id FROM companies LIMIT 1);
INSERT IGNORE INTO lookup_values (company_id, list_key, code, name_en, name_ar, sort_order) VALUES
(@company_id, 'company_type', 'LLC', 'LLC', NULL, 1),
(@company_id, 'company_type', 'Branch', 'Branch', NULL, 2),
(@company_id, 'company_type', 'SoleProp', 'Sole Proprietor', NULL, 3),
(@company_id, 'industry', 'Trading', 'Trading', NULL, 1),
(@company_id, 'industry', 'Services', 'Services', NULL, 2),
(@company_id, 'customer_group', 'KEY', 'Key Accounts', NULL, 1),
(@company_id, 'customer_segment', 'Corporate', 'Corporate', NULL, 1),
(@company_id, 'customer_segment', 'SMB', 'SMB', NULL, 2),
(@company_id, 'vendor_group', 'STRATEGIC', 'Strategic', NULL, 1),
(@company_id, 'vendor_segment', 'Local', 'Local', NULL, 1),
(@company_id, 'vendor_segment', 'International', 'International', NULL, 2),
(@company_id, 'shipping_method', 'Road', 'Road', NULL, 1),
(@company_id, 'shipping_method', 'Air', 'Air', NULL, 2),
(@company_id, 'delivery_priority', 'Normal', 'Normal', NULL, 1),
(@company_id, 'delivery_priority', 'Urgent', 'Urgent', NULL, 2),
(@company_id, 'po_type', 'Standard', 'Standard', NULL, 1),
(@company_id, 'po_type', 'Service', 'Service', NULL, 2),
(@company_id, 'price_control_type', 'Fixed', 'Fixed', NULL, 1),
(@company_id, 'price_control_type', 'Variable', 'Variable', NULL, 2),
(@company_id, 'invoice_matching_type', '2-Way', '2-Way', NULL, 1),
(@company_id, 'invoice_matching_type', '3-Way', '3-Way', NULL, 2),
(@company_id, 'invoice_matching_rule', 'Standard', 'Standard', NULL, 1),
(@company_id, 'approval_workflow', 'STD', 'Standard Approval', NULL, 1),
(@company_id, 'nda_reference', 'NDA-STD', 'Standard NDA', NULL, 1),
(@company_id, 'payment_terms', 'NET30', 'Net 30 Days', NULL, 1),
(@company_id, 'incoterms', 'EXW', 'EXW', NULL, 1),
(@company_id, 'incoterms', 'CIF', 'CIF', NULL, 2),
(@company_id, 'delivery_terms', 'Delivered', 'Delivered', NULL, 1),
(@company_id, 'accounting_standard', 'IFRS', 'IFRS', NULL, 1),
(@company_id, 'accounting_standard', 'GAAP', 'GAAP', NULL, 2),
(@company_id, 'chart_of_accounts', 'COA-STD', 'Standard COA', NULL, 1),
(@company_id, 'chart_of_accounts', 'COA-IFRS', 'IFRS COA', NULL, 2),
(@company_id, 'cost_center_structure', 'CC-DEP', 'By Department', NULL, 1),
(@company_id, 'cost_center_structure', 'CC-PROJ', 'By Project', NULL, 2),
(@company_id, 'profit_center_structure', 'PC-BU', 'By Business Unit', NULL, 1),
(@company_id, 'profit_center_structure', 'PC-REG', 'By Region', NULL, 2),
(@company_id, 'timezone', 'Asia/Riyadh', 'Asia/Riyadh (UTC+3)', NULL, 1),
(@company_id, 'timezone', 'Asia/Dubai', 'Asia/Dubai (UTC+4)', NULL, 2),
(@company_id, 'timezone', 'UTC', 'UTC', NULL, 3),
(@company_id, 'contact_role', 'PROC', 'Procurement', NULL, 1),
(@company_id, 'contact_role', 'FIN', 'Finance', NULL, 2),
(@company_id, 'contact_role', 'SALES', 'Sales', NULL, 3),
(@company_id, 'employee_category', 'FULLTIME', 'Full Time', NULL, 1),
(@company_id, 'employee_category', 'PARTTIME', 'Part Time', NULL, 2),
(@company_id, 'employee_category', 'CONTRACT', 'Contract', NULL, 3),
(@company_id, 'business_unit', 'OPS', 'Operations', NULL, 1),
(@company_id, 'business_unit', 'SALES', 'Sales', NULL, 2),
(@company_id, 'business_unit', 'HR', 'Human Resources', NULL, 3),
(@company_id, 'position_title', 'MGR', 'Manager', NULL, 1),
(@company_id, 'position_title', 'ANALYST', 'Analyst', NULL, 2),
(@company_id, 'grade_level', 'G1', 'Grade 1', NULL, 1),
(@company_id, 'grade_level', 'G2', 'Grade 2', NULL, 2),
(@company_id, 'marital_status', 'SINGLE', 'Single', NULL, 1),
(@company_id, 'marital_status', 'MARRIED', 'Married', NULL, 2),
(@company_id, 'blood_group', 'A+', 'A+', NULL, 1),
(@company_id, 'blood_group', 'B+', 'B+', NULL, 2),
(@company_id, 'blood_group', 'O+', 'O+', NULL, 3),
(@company_id, 'blood_group', 'AB+', 'AB+', NULL, 4),
(@company_id, 'blood_group', 'A-', 'A-', NULL, 5),
(@company_id, 'blood_group', 'B-', 'B-', NULL, 6),
(@company_id, 'blood_group', 'O-', 'O-', NULL, 7),
(@company_id, 'blood_group', 'AB-', 'AB-', NULL, 8),
(@company_id, 'visa_type', 'WORK', 'Work Visa', NULL, 1),
(@company_id, 'visa_type', 'BUSINESS', 'Business Visa', NULL, 2),
(@company_id, 'visa_type', 'VISIT', 'Visit Visa', NULL, 3),
(@company_id, 'product_type', 'product', 'Product', NULL, 1),
(@company_id, 'product_type', 'service', 'Service', NULL, 2),
(@company_id, 'plate_type', 'PRIVATE', 'Private', NULL, 1),
(@company_id, 'plate_type', 'COMMERCIAL', 'Commercial', NULL, 2),
(@company_id, 'vehicle_type', 'SEDAN', 'Sedan', NULL, 1),
(@company_id, 'vehicle_type', 'SUV', 'SUV', NULL, 2),
(@company_id, 'vehicle_type', 'TRUCK', 'Truck', NULL, 3),
(@company_id, 'risk_category', 'LOW', 'Low', NULL, 1),
(@company_id, 'risk_category', 'MEDIUM', 'Medium', NULL, 2),
(@company_id, 'risk_category', 'HIGH', 'High', NULL, 3),
(@company_id, 'file_category', 'CONTRACT', 'Contract', NULL, 1),
(@company_id, 'file_category', 'INVOICE', 'Invoice', NULL, 2),
(@company_id, 'file_category', 'HR', 'HR Document', NULL, 3),
(@company_id, 'document_type', 'AGREEMENT', 'Agreement', NULL, 1),
(@company_id, 'document_type', 'CERTIFICATE', 'Certificate', NULL, 2),
(@company_id, 'document_type', 'REPORT', 'Report', NULL, 3),
(@company_id, 'access_level', 'PUBLIC', 'Public', NULL, 1),
(@company_id, 'access_level', 'INTERNAL', 'Internal', NULL, 2),
(@company_id, 'access_level', 'RESTRICTED', 'Restricted', NULL, 3),
(@company_id, 'confidentiality_level', 'LOW', 'Low', NULL, 1),
(@company_id, 'confidentiality_level', 'MEDIUM', 'Medium', NULL, 2),
(@company_id, 'confidentiality_level', 'HIGH', 'High', NULL, 3),
(@company_id, 'data_classification', 'PUBLIC', 'Public', NULL, 1),
(@company_id, 'data_classification', 'INTERNAL', 'Internal', NULL, 2),
(@company_id, 'data_classification', 'CONFIDENTIAL', 'Confidential', NULL, 3),
(@company_id, 'sales_org', 'SO1', 'Sales Org 1', NULL, 1),
(@company_id, 'sales_org', 'SO2', 'Sales Org 2', NULL, 2),
(@company_id, 'distribution_channel', 'DIRECT', 'Direct', NULL, 1),
(@company_id, 'distribution_channel', 'ONLINE', 'Online', NULL, 2),
(@company_id, 'division', 'CONSUMER', 'Consumer', NULL, 1),
(@company_id, 'division', 'ENTERPRISE', 'Enterprise', NULL, 2),
(@company_id, 'branch_location', 'Riyadh', 'Riyadh', NULL, 1),
(@company_id, 'branch_location', 'Jeddah', 'Jeddah', NULL, 2);
