-- Enterprise Expansion (2026-02-08)
USE erp1;

-- Companies
ALTER TABLE companies
  ADD COLUMN company_code VARCHAR(50) NULL,
  ADD COLUMN trade_name VARCHAR(200) NULL,
  ADD COLUMN company_type VARCHAR(50) NULL,
  ADD COLUMN industry VARCHAR(150) NULL,
  ADD COLUMN parent_company_id BIGINT UNSIGNED NULL,
  ADD COLUMN is_subsidiary TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN company_status VARCHAR(20) NOT NULL DEFAULT 'active',
  ADD COLUMN legal_jurisdiction_country VARCHAR(100) NULL;

ALTER TABLE companies
  ADD COLUMN cr_issue_date DATE NULL,
  ADD COLUMN cr_expiry_date DATE NULL,
  ADD COLUMN cr_issuing_authority VARCHAR(150) NULL,
  ADD COLUMN chamber_membership_no VARCHAR(100) NULL,
  ADD COLUMN chamber_expiry_date DATE NULL,
  ADD COLUMN unified_national_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_expiry DATE NULL,
  ADD COLUMN aoa_reference VARCHAR(150) NULL;

ALTER TABLE companies
  ADD COLUMN intl_registration_no VARCHAR(100) NULL,
  ADD COLUMN incorporation_date DATE NULL,
  ADD COLUMN incorporation_country VARCHAR(100) NULL,
  ADD COLUMN incorporation_authority VARCHAR(150) NULL;

ALTER TABLE companies
  ADD COLUMN vat_effective_date DATE NULL,
  ADD COLUMN vat_status VARCHAR(20) NULL,
  ADD COLUMN zatca_registration_status VARCHAR(50) NULL,
  ADD COLUMN zakat_file_no VARCHAR(100) NULL,
  ADD COLUMN tax_identification_no VARCHAR(100) NULL,
  ADD COLUMN withholding_tax_applicable TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN zakat_tax_percentage DECIMAL(6,3) NULL,
  ADD COLUMN gst_number VARCHAR(100) NULL,
  ADD COLUMN sales_tax_number VARCHAR(100) NULL,
  ADD COLUMN corporate_tax_number VARCHAR(100) NULL,
  ADD COLUMN tax_residency_country VARCHAR(100) NULL,
  ADD COLUMN double_tax_treaty_applicable TINYINT NOT NULL DEFAULT 0;

ALTER TABLE companies
  ADD COLUMN national_address_id VARCHAR(100) NULL,
  ADD COLUMN building_no VARCHAR(20) NULL,
  ADD COLUMN street_name VARCHAR(150) NULL,
  ADD COLUMN district VARCHAR(150) NULL,
  ADD COLUMN city VARCHAR(150) NULL,
  ADD COLUMN postal_code VARCHAR(20) NULL,
  ADD COLUMN additional_no VARCHAR(20) NULL,
  ADD COLUMN short_address VARCHAR(50) NULL,
  ADD COLUMN address_type VARCHAR(50) NULL,
  ADD COLUMN intl_address_line1 VARCHAR(150) NULL,
  ADD COLUMN intl_address_line2 VARCHAR(150) NULL,
  ADD COLUMN intl_city VARCHAR(150) NULL,
  ADD COLUMN intl_state VARCHAR(150) NULL,
  ADD COLUMN intl_postal_code VARCHAR(20) NULL,
  ADD COLUMN intl_country VARCHAR(100) NULL,
  ADD COLUMN intl_address_type VARCHAR(50) NULL;

ALTER TABLE companies
  ADD COLUMN phone_main VARCHAR(50) NULL,
  ADD COLUMN phone_secondary VARCHAR(50) NULL,
  ADD COLUMN mobile VARCHAR(50) NULL,
  ADD COLUMN fax VARCHAR(50) NULL,
  ADD COLUMN email VARCHAR(150) NULL,
  ADD COLUMN support_email VARCHAR(150) NULL,
  ADD COLUMN website VARCHAR(150) NULL,
  ADD COLUMN social_links JSON NULL;

ALTER TABLE companies
  ADD COLUMN bank_name VARCHAR(150) NULL,
  ADD COLUMN bank_branch VARCHAR(150) NULL,
  ADD COLUMN bank_account_name VARCHAR(150) NULL,
  ADD COLUMN bank_account_number VARCHAR(100) NULL,
  ADD COLUMN iban VARCHAR(50) NULL,
  ADD COLUMN swift_code VARCHAR(50) NULL,
  ADD COLUMN bank_currency_code CHAR(3) NULL,
  ADD COLUMN bank_country VARCHAR(100) NULL,
  ADD COLUMN default_bank_account_id BIGINT UNSIGNED NULL;

ALTER TABLE companies
  ADD COLUMN owner_name VARCHAR(150) NULL,
  ADD COLUMN owner_nationality VARCHAR(100) NULL,
  ADD COLUMN owner_id_no VARCHAR(100) NULL,
  ADD COLUMN ownership_percentage DECIMAL(6,3) NULL,
  ADD COLUMN board_of_directors TEXT NULL,
  ADD COLUMN general_manager_name VARCHAR(150) NULL,
  ADD COLUMN authorized_signatory_name VARCHAR(150) NULL,
  ADD COLUMN signatory_id_no VARCHAR(100) NULL,
  ADD COLUMN signatory_title VARCHAR(100) NULL,
  ADD COLUMN power_of_attorney_ref VARCHAR(150) NULL;

ALTER TABLE companies
  ADD COLUMN secondary_currency_code CHAR(3) NULL,
  ADD COLUMN fiscal_year_start DATE NULL,
  ADD COLUMN fiscal_year_end DATE NULL,
  ADD COLUMN accounting_standard VARCHAR(50) NULL,
  ADD COLUMN chart_of_accounts_ref VARCHAR(100) NULL,
  ADD COLUMN cost_center_structure VARCHAR(100) NULL,
  ADD COLUMN profit_center_structure VARCHAR(100) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_limit DECIMAL(18,2) NOT NULL DEFAULT 0;

ALTER TABLE companies
  ADD COLUMN gosi_no VARCHAR(100) NULL,
  ADD COLUMN gosi_office VARCHAR(100) NULL,
  ADD COLUMN labor_office_no VARCHAR(100) NULL,
  ADD COLUMN saudization_level VARCHAR(50) NULL,
  ADD COLUMN establishment_id VARCHAR(100) NULL,
  ADD COLUMN employee_capacity INT NULL;

ALTER TABLE companies
  ADD COLUMN warehouse_locations TEXT NULL,
  ADD COLUMN default_warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN importer_code VARCHAR(100) NULL,
  ADD COLUMN exporter_code VARCHAR(100) NULL,
  ADD COLUMN customs_reg_no VARCHAR(100) NULL,
  ADD COLUMN port_of_entry VARCHAR(100) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL;

ALTER TABLE companies
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN is_active TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN blocked_sales TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_purchasing TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_payments TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN risk_category VARCHAR(50) NULL;

ALTER TABLE companies
  ADD CONSTRAINT fk_companies_parent FOREIGN KEY (parent_company_id) REFERENCES companies(id),
  ADD CONSTRAINT fk_companies_secondary_currency FOREIGN KEY (secondary_currency_code) REFERENCES currencies(code),
  ADD CONSTRAINT fk_companies_default_wh FOREIGN KEY (default_warehouse_id) REFERENCES warehouses(id),
  ADD CONSTRAINT fk_companies_default_bank FOREIGN KEY (default_bank_account_id) REFERENCES bank_accounts(id),
  ADD CONSTRAINT fk_companies_bank_currency FOREIGN KEY (bank_currency_code) REFERENCES currencies(code);
-- Customers
ALTER TABLE customers
  ADD COLUMN customer_code VARCHAR(50) NULL,
  ADD COLUMN trade_name VARCHAR(200) NULL,
  ADD COLUMN company_type VARCHAR(50) NULL,
  ADD COLUMN industry VARCHAR(150) NULL,
  ADD COLUMN parent_customer_id BIGINT UNSIGNED NULL,
  ADD COLUMN is_subsidiary TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN customer_status VARCHAR(20) NOT NULL DEFAULT 'active',
  ADD COLUMN legal_jurisdiction_country VARCHAR(100) NULL;

ALTER TABLE customers
  ADD COLUMN cr_issue_date DATE NULL,
  ADD COLUMN cr_expiry_date DATE NULL,
  ADD COLUMN cr_issuing_authority VARCHAR(150) NULL,
  ADD COLUMN chamber_membership_no VARCHAR(100) NULL,
  ADD COLUMN chamber_expiry_date DATE NULL,
  ADD COLUMN unified_national_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_expiry DATE NULL,
  ADD COLUMN aoa_reference VARCHAR(150) NULL;

ALTER TABLE customers
  ADD COLUMN intl_registration_no VARCHAR(100) NULL,
  ADD COLUMN incorporation_date DATE NULL,
  ADD COLUMN incorporation_country VARCHAR(100) NULL,
  ADD COLUMN incorporation_authority VARCHAR(150) NULL;

ALTER TABLE customers
  ADD COLUMN vat_effective_date DATE NULL,
  ADD COLUMN vat_status VARCHAR(20) NULL,
  ADD COLUMN zatca_registration_status VARCHAR(50) NULL,
  ADD COLUMN zakat_file_no VARCHAR(100) NULL,
  ADD COLUMN tax_identification_no VARCHAR(100) NULL,
  ADD COLUMN withholding_tax_applicable TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN zakat_tax_percentage DECIMAL(6,3) NULL,
  ADD COLUMN gst_number VARCHAR(100) NULL,
  ADD COLUMN sales_tax_number VARCHAR(100) NULL,
  ADD COLUMN corporate_tax_number VARCHAR(100) NULL,
  ADD COLUMN tax_residency_country VARCHAR(100) NULL,
  ADD COLUMN double_tax_treaty_applicable TINYINT NOT NULL DEFAULT 0;

ALTER TABLE customers
  ADD COLUMN national_address_id VARCHAR(100) NULL,
  ADD COLUMN building_no VARCHAR(20) NULL,
  ADD COLUMN street_name VARCHAR(150) NULL,
  ADD COLUMN district VARCHAR(150) NULL,
  ADD COLUMN city VARCHAR(150) NULL,
  ADD COLUMN postal_code VARCHAR(20) NULL,
  ADD COLUMN additional_no VARCHAR(20) NULL,
  ADD COLUMN short_address VARCHAR(50) NULL,
  ADD COLUMN address_type VARCHAR(50) NULL,
  ADD COLUMN intl_address_line1 VARCHAR(150) NULL,
  ADD COLUMN intl_address_line2 VARCHAR(150) NULL,
  ADD COLUMN intl_city VARCHAR(150) NULL,
  ADD COLUMN intl_state VARCHAR(150) NULL,
  ADD COLUMN intl_postal_code VARCHAR(20) NULL,
  ADD COLUMN intl_country VARCHAR(100) NULL,
  ADD COLUMN intl_address_type VARCHAR(50) NULL;

ALTER TABLE customers
  ADD COLUMN phone_main VARCHAR(50) NULL,
  ADD COLUMN phone_secondary VARCHAR(50) NULL,
  ADD COLUMN mobile VARCHAR(50) NULL,
  ADD COLUMN fax VARCHAR(50) NULL,
  ADD COLUMN email VARCHAR(150) NULL,
  ADD COLUMN support_email VARCHAR(150) NULL,
  ADD COLUMN website VARCHAR(150) NULL,
  ADD COLUMN social_links JSON NULL;

ALTER TABLE customers
  ADD COLUMN bank_name VARCHAR(150) NULL,
  ADD COLUMN bank_branch VARCHAR(150) NULL,
  ADD COLUMN bank_account_name VARCHAR(150) NULL,
  ADD COLUMN bank_account_number VARCHAR(100) NULL,
  ADD COLUMN iban VARCHAR(50) NULL,
  ADD COLUMN swift_code VARCHAR(50) NULL,
  ADD COLUMN bank_currency_code CHAR(3) NULL,
  ADD COLUMN bank_country VARCHAR(100) NULL,
  ADD COLUMN default_bank_flag TINYINT NOT NULL DEFAULT 0;

ALTER TABLE customers
  ADD COLUMN owner_name VARCHAR(150) NULL,
  ADD COLUMN owner_nationality VARCHAR(100) NULL,
  ADD COLUMN owner_id_no VARCHAR(100) NULL,
  ADD COLUMN ownership_percentage DECIMAL(6,3) NULL,
  ADD COLUMN board_of_directors TEXT NULL,
  ADD COLUMN general_manager_name VARCHAR(150) NULL,
  ADD COLUMN authorized_signatory_name VARCHAR(150) NULL,
  ADD COLUMN signatory_id_no VARCHAR(100) NULL,
  ADD COLUMN signatory_title VARCHAR(100) NULL,
  ADD COLUMN power_of_attorney_ref VARCHAR(150) NULL;

ALTER TABLE customers
  ADD COLUMN customer_group VARCHAR(100) NULL,
  ADD COLUMN customer_segment VARCHAR(100) NULL,
  ADD COLUMN bill_to_party VARCHAR(150) NULL,
  ADD COLUMN ship_to_party VARCHAR(150) NULL,
  ADD COLUMN payer VARCHAR(150) NULL,
  ADD COLUMN end_customer_name VARCHAR(150) NULL,
  ADD COLUMN accounting_standard VARCHAR(50) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_exposure DECIMAL(18,2) NOT NULL DEFAULT 0;

ALTER TABLE customers
  ADD COLUMN gosi_no VARCHAR(100) NULL,
  ADD COLUMN importer_code VARCHAR(100) NULL,
  ADD COLUMN exporter_code VARCHAR(100) NULL,
  ADD COLUMN customs_reg_no VARCHAR(100) NULL,
  ADD COLUMN port_of_entry VARCHAR(100) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL;

ALTER TABLE customers
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN blocked_sales TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_purchasing TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_payments TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN risk_category VARCHAR(50) NULL;

ALTER TABLE customers
  ADD CONSTRAINT fk_customers_parent FOREIGN KEY (parent_customer_id) REFERENCES customers(id),
  ADD CONSTRAINT fk_customers_bank_currency FOREIGN KEY (bank_currency_code) REFERENCES currencies(code);

-- Vendors
ALTER TABLE vendors
  ADD COLUMN vendor_code VARCHAR(50) NULL,
  ADD COLUMN trade_name VARCHAR(200) NULL,
  ADD COLUMN company_type VARCHAR(50) NULL,
  ADD COLUMN industry VARCHAR(150) NULL,
  ADD COLUMN parent_vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN is_subsidiary TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN vendor_status VARCHAR(20) NOT NULL DEFAULT 'active',
  ADD COLUMN legal_jurisdiction_country VARCHAR(100) NULL;

ALTER TABLE vendors
  ADD COLUMN cr_issue_date DATE NULL,
  ADD COLUMN cr_expiry_date DATE NULL,
  ADD COLUMN cr_issuing_authority VARCHAR(150) NULL,
  ADD COLUMN chamber_membership_no VARCHAR(100) NULL,
  ADD COLUMN chamber_expiry_date DATE NULL,
  ADD COLUMN unified_national_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_no VARCHAR(100) NULL,
  ADD COLUMN misa_license_expiry DATE NULL,
  ADD COLUMN aoa_reference VARCHAR(150) NULL;

ALTER TABLE vendors
  ADD COLUMN intl_registration_no VARCHAR(100) NULL,
  ADD COLUMN incorporation_date DATE NULL,
  ADD COLUMN incorporation_country VARCHAR(100) NULL,
  ADD COLUMN incorporation_authority VARCHAR(150) NULL;

ALTER TABLE vendors
  ADD COLUMN vat_effective_date DATE NULL,
  ADD COLUMN vat_status VARCHAR(20) NULL,
  ADD COLUMN zatca_registration_status VARCHAR(50) NULL,
  ADD COLUMN zakat_file_no VARCHAR(100) NULL,
  ADD COLUMN tax_identification_no VARCHAR(100) NULL,
  ADD COLUMN withholding_tax_applicable TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN zakat_tax_percentage DECIMAL(6,3) NULL,
  ADD COLUMN gst_number VARCHAR(100) NULL,
  ADD COLUMN sales_tax_number VARCHAR(100) NULL,
  ADD COLUMN corporate_tax_number VARCHAR(100) NULL,
  ADD COLUMN tax_residency_country VARCHAR(100) NULL,
  ADD COLUMN double_tax_treaty_applicable TINYINT NOT NULL DEFAULT 0;

ALTER TABLE vendors
  ADD COLUMN national_address_id VARCHAR(100) NULL,
  ADD COLUMN building_no VARCHAR(20) NULL,
  ADD COLUMN street_name VARCHAR(150) NULL,
  ADD COLUMN district VARCHAR(150) NULL,
  ADD COLUMN city VARCHAR(150) NULL,
  ADD COLUMN postal_code VARCHAR(20) NULL,
  ADD COLUMN additional_no VARCHAR(20) NULL,
  ADD COLUMN short_address VARCHAR(50) NULL,
  ADD COLUMN address_type VARCHAR(50) NULL,
  ADD COLUMN intl_address_line1 VARCHAR(150) NULL,
  ADD COLUMN intl_address_line2 VARCHAR(150) NULL,
  ADD COLUMN intl_city VARCHAR(150) NULL,
  ADD COLUMN intl_state VARCHAR(150) NULL,
  ADD COLUMN intl_postal_code VARCHAR(20) NULL,
  ADD COLUMN intl_country VARCHAR(100) NULL,
  ADD COLUMN intl_address_type VARCHAR(50) NULL;

ALTER TABLE vendors
  ADD COLUMN phone_main VARCHAR(50) NULL,
  ADD COLUMN phone_secondary VARCHAR(50) NULL,
  ADD COLUMN mobile VARCHAR(50) NULL,
  ADD COLUMN fax VARCHAR(50) NULL,
  ADD COLUMN email VARCHAR(150) NULL,
  ADD COLUMN support_email VARCHAR(150) NULL,
  ADD COLUMN website VARCHAR(150) NULL,
  ADD COLUMN social_links JSON NULL;

ALTER TABLE vendors
  ADD COLUMN bank_name VARCHAR(150) NULL,
  ADD COLUMN bank_branch VARCHAR(150) NULL,
  ADD COLUMN bank_account_name VARCHAR(150) NULL,
  ADD COLUMN bank_account_number VARCHAR(100) NULL,
  ADD COLUMN iban VARCHAR(50) NULL,
  ADD COLUMN swift_code VARCHAR(50) NULL,
  ADD COLUMN bank_currency_code CHAR(3) NULL,
  ADD COLUMN bank_country VARCHAR(100) NULL,
  ADD COLUMN default_bank_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN default_bank_account_id BIGINT UNSIGNED NULL;

ALTER TABLE vendors
  ADD COLUMN owner_name VARCHAR(150) NULL,
  ADD COLUMN owner_nationality VARCHAR(100) NULL,
  ADD COLUMN owner_id_no VARCHAR(100) NULL,
  ADD COLUMN ownership_percentage DECIMAL(6,3) NULL,
  ADD COLUMN board_of_directors TEXT NULL,
  ADD COLUMN general_manager_name VARCHAR(150) NULL,
  ADD COLUMN authorized_signatory_name VARCHAR(150) NULL,
  ADD COLUMN signatory_id_no VARCHAR(100) NULL,
  ADD COLUMN signatory_title VARCHAR(100) NULL,
  ADD COLUMN power_of_attorney_ref VARCHAR(150) NULL;

ALTER TABLE vendors
  ADD COLUMN vendor_group VARCHAR(100) NULL,
  ADD COLUMN vendor_segment VARCHAR(100) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_exposure DECIMAL(18,2) NOT NULL DEFAULT 0;

ALTER TABLE vendors
  ADD COLUMN importer_code VARCHAR(100) NULL,
  ADD COLUMN exporter_code VARCHAR(100) NULL,
  ADD COLUMN customs_reg_no VARCHAR(100) NULL,
  ADD COLUMN port_of_entry VARCHAR(100) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL;

ALTER TABLE vendors
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN blocked_sales TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_purchasing TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blocked_payments TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN risk_category VARCHAR(50) NULL;

ALTER TABLE vendors
  ADD CONSTRAINT fk_vendors_parent FOREIGN KEY (parent_vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_vendors_bank_currency FOREIGN KEY (bank_currency_code) REFERENCES currencies(code),
  ADD CONSTRAINT fk_vendors_default_bank FOREIGN KEY (default_bank_account_id) REFERENCES bank_accounts(id);
-- Contacts (Enterprise)
ALTER TABLE contacts
  ADD COLUMN uuid CHAR(36) NULL,
  ADD COLUMN contact_type VARCHAR(50) NULL,
  ADD COLUMN contact_role VARCHAR(100) NULL,
  ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active',
  ADD COLUMN priority_level VARCHAR(20) NULL,
  ADD COLUMN confidentiality_level VARCHAR(20) NULL,
  ADD COLUMN first_name VARCHAR(100) NULL,
  ADD COLUMN middle_name VARCHAR(100) NULL,
  ADD COLUMN last_name VARCHAR(100) NULL,
  ADD COLUMN full_name VARCHAR(200) NULL,
  ADD COLUMN name_ar_full VARCHAR(200) NULL,
  ADD COLUMN gender VARCHAR(20) NULL,
  ADD COLUMN date_of_birth DATE NULL,
  ADD COLUMN nationality VARCHAR(100) NULL,
  ADD COLUMN preferred_language VARCHAR(50) NULL,
  ADD COLUMN title VARCHAR(20) NULL,
  ADD COLUMN national_id_number VARCHAR(100) NULL,
  ADD COLUMN iqama_number VARCHAR(100) NULL,
  ADD COLUMN passport_number VARCHAR(100) NULL,
  ADD COLUMN passport_issuing_country VARCHAR(100) NULL,
  ADD COLUMN passport_expiry_date DATE NULL,
  ADD COLUMN absher_registered TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN government_id_type VARCHAR(50) NULL,
  ADD COLUMN government_id_number VARCHAR(100) NULL,
  ADD COLUMN id_issuing_authority VARCHAR(100) NULL,
  ADD COLUMN id_expiry_date DATE NULL,
  ADD COLUMN kyc_verified TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN aml_checked TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN sanction_screened TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN pep_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN department VARCHAR(150) NULL,
  ADD COLUMN job_title VARCHAR(150) NULL,
  ADD COLUMN employment_type VARCHAR(50) NULL,
  ADD COLUMN start_date DATE NULL,
  ADD COLUMN end_date DATE NULL,
  ADD COLUMN is_primary TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN reporting_manager_contact_id BIGINT UNSIGNED NULL,
  ADD COLUMN preferred_contact_method VARCHAR(50) NULL,
  ADD COLUMN preferred_contact_time VARCHAR(50) NULL,
  ADD COLUMN do_not_call TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN do_not_email TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN whatsapp_consent TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN marketing_consent TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN sms_consent TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN authorized_signatory TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approval_limit_amount DECIMAL(18,2) NULL,
  ADD COLUMN approval_currency_code CHAR(3) NULL,
  ADD COLUMN payment_approval_authority TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN contract_signing_authority TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN bank_authorization_ref VARCHAR(150) NULL,
  ADD COLUMN user_id BIGINT UNSIGNED NULL,
  ADD COLUMN system_role VARCHAR(100) NULL,
  ADD COLUMN mfa_enabled TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN last_login_date DATETIME NULL,
  ADD COLUMN account_locked TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN relationship_type VARCHAR(50) NULL,
  ADD COLUMN account_owner_user_id BIGINT UNSIGNED NULL,
  ADD COLUMN assigned_sales_rep_id BIGINT UNSIGNED NULL,
  ADD COLUMN assigned_account_manager_id BIGINT UNSIGNED NULL,
  ADD COLUMN interaction_frequency VARCHAR(50) NULL,
  ADD COLUMN last_interaction_date DATE NULL,
  ADD COLUMN notes TEXT NULL,
  ADD COLUMN internal_comments TEXT NULL,
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN source_system VARCHAR(50) NULL,
  ADD COLUMN data_sync_status VARCHAR(50) NULL,
  ADD COLUMN record_status VARCHAR(20) NULL,
  ADD COLUMN active_flag TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN blocked_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN legal_hold_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN blacklisted_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN data_retention_expiry_date DATE NULL;

ALTER TABLE contacts
  ADD CONSTRAINT fk_contacts_reporting_manager FOREIGN KEY (reporting_manager_contact_id) REFERENCES contacts(id),
  ADD CONSTRAINT fk_contacts_user FOREIGN KEY (user_id) REFERENCES users(id),
  ADD CONSTRAINT fk_contacts_account_owner FOREIGN KEY (account_owner_user_id) REFERENCES users(id),
  ADD CONSTRAINT fk_contacts_sales_rep FOREIGN KEY (assigned_sales_rep_id) REFERENCES users(id),
  ADD CONSTRAINT fk_contacts_account_manager FOREIGN KEY (assigned_account_manager_id) REFERENCES users(id),
  ADD CONSTRAINT fk_contacts_approval_currency FOREIGN KEY (approval_currency_code) REFERENCES currencies(code);

CREATE TABLE IF NOT EXISTS contact_phones (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_id BIGINT UNSIGNED NOT NULL,
  phone_type VARCHAR(20) NOT NULL,
  country_code VARCHAR(10) NULL,
  phone_number VARCHAR(30) NOT NULL,
  extension VARCHAR(10) NULL,
  is_primary TINYINT NOT NULL DEFAULT 0,
  is_verified TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_contact_phones_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_emails (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_id BIGINT UNSIGNED NOT NULL,
  email VARCHAR(150) NOT NULL,
  email_type VARCHAR(20) NULL,
  is_primary TINYINT NOT NULL DEFAULT 0,
  is_verified TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_contact_emails_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_addresses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  contact_id BIGINT UNSIGNED NOT NULL,
  address_type VARCHAR(50) NULL,
  is_ksa_address TINYINT NOT NULL DEFAULT 1,
  building_no VARCHAR(20) NULL,
  street_name VARCHAR(150) NULL,
  district VARCHAR(150) NULL,
  city VARCHAR(150) NULL,
  postal_code VARCHAR(20) NULL,
  additional_no VARCHAR(20) NULL,
  short_address VARCHAR(50) NULL,
  address_line1 VARCHAR(150) NULL,
  address_line2 VARCHAR(150) NULL,
  state_province VARCHAR(150) NULL,
  country VARCHAR(100) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_contact_addresses_contact FOREIGN KEY (contact_id) REFERENCES contacts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Customer price agreements (contract rates)
CREATE TABLE IF NOT EXISTS customer_price_agreements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  customer_id BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  currency_code CHAR(3) NOT NULL,
  unit_price DECIMAL(18,4) NOT NULL,
  start_date DATE NULL,
  end_date DATE NULL,
  contract_ref VARCHAR(100) NULL,
  is_active TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL,
  UNIQUE KEY uq_cpa (company_id, customer_id, product_id, currency_code, start_date),
  CONSTRAINT fk_cpa_company FOREIGN KEY (company_id) REFERENCES companies(id),
  CONSTRAINT fk_cpa_customer FOREIGN KEY (customer_id) REFERENCES customers(id),
  CONSTRAINT fk_cpa_product FOREIGN KEY (product_id) REFERENCES products(id),
  CONSTRAINT fk_cpa_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- RFQ / Procurement expansion
ALTER TABLE rfqs
  ADD COLUMN rfq_type VARCHAR(30) NULL,
  ADD COLUMN rfq_version VARCHAR(20) NULL,
  ADD COLUMN reference_rfq_no VARCHAR(50) NULL,
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN requesting_department VARCHAR(100) NULL,
  ADD COLUMN requester_id BIGINT UNSIGNED NULL,
  ADD COLUMN buyer_id BIGINT UNSIGNED NULL,
  ADD COLUMN approval_workflow_id VARCHAR(50) NULL,
  ADD COLUMN primary_vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN vendor_contact_id BIGINT UNSIGNED NULL,
  ADD COLUMN sourcing_type VARCHAR(30) NULL,
  ADD COLUMN bid_submission_method VARCHAR(30) NULL,
  ADD COLUMN bid_currency_code CHAR(3) NULL,
  ADD COLUMN bid_exchange_rate DECIMAL(18,6) NOT NULL DEFAULT 1,
  ADD COLUMN rfq_issue_date DATE NULL,
  ADD COLUMN bid_submission_deadline DATE NULL,
  ADD COLUMN rfq_validity_date DATE NULL,
  ADD COLUMN expected_award_date DATE NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN delivery_location VARCHAR(150) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN tax_scheme VARCHAR(50) NULL,
  ADD COLUMN price_basis VARCHAR(50) NULL,
  ADD COLUMN contract_reference VARCHAR(100) NULL,
  ADD COLUMN nda_reference VARCHAR(100) NULL,
  ADD COLUMN header_notes TEXT NULL,
  ADD COLUMN technical_specs_ref VARCHAR(150) NULL,
  ADD COLUMN attachments_ref VARCHAR(150) NULL,
  ADD COLUMN po_type VARCHAR(30) NULL,
  ADD COLUMN price_control_type VARCHAR(30) NULL,
  ADD COLUMN delivery_address_id VARCHAR(100) NULL,
  ADD COLUMN invoice_matching_type VARCHAR(30) NULL,
  ADD COLUMN acceptance_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN receipt_required TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN service_acceptance_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN inspection_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN tolerance_qty_pct DECIMAL(6,3) NULL,
  ADD COLUMN tolerance_price_pct DECIMAL(6,3) NULL,
  ADD COLUMN over_delivery_pct DECIMAL(6,3) NULL,
  ADD COLUMN invoice_currency_code CHAR(3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN withholding_tax_pct DECIMAL(6,3) NULL,
  ADD COLUMN invoice_matching_rule VARCHAR(50) NULL,
  ADD COLUMN retention_pct DECIMAL(6,3) NULL,
  ADD COLUMN milestone_billing TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN advance_payment_pct DECIMAL(6,3) NULL,
  ADD COLUMN budget_check TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN budget_reference VARCHAR(100) NULL,
  ADD COLUMN approval_matrix VARCHAR(100) NULL,
  ADD COLUMN risk_level VARCHAR(30) NULL,
  ADD COLUMN awarded_vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN award_date DATE NULL,
  ADD COLUMN awarded_by BIGINT UNSIGNED NULL,
  ADD COLUMN award_notes TEXT NULL,
  ADD COLUMN rfq_closed_after_po TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN po_balance_remaining DECIMAL(18,2) NULL,
  ADD COLUMN invoiced_amount DECIMAL(18,2) NULL,
  ADD COLUMN grn_quantity DECIMAL(18,4) NULL,
  ADD COLUMN invoice_hold_flag TINYINT NOT NULL DEFAULT 0;

ALTER TABLE rfqs
  ADD CONSTRAINT fk_rfq_requester FOREIGN KEY (requester_id) REFERENCES users(id),
  ADD CONSTRAINT fk_rfq_buyer FOREIGN KEY (buyer_id) REFERENCES users(id),
  ADD CONSTRAINT fk_rfq_primary_vendor FOREIGN KEY (primary_vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_rfq_vendor_contact FOREIGN KEY (vendor_contact_id) REFERENCES contacts(id),
  ADD CONSTRAINT fk_rfq_awarded_vendor FOREIGN KEY (awarded_vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_rfq_awarded_by FOREIGN KEY (awarded_by) REFERENCES users(id),
  ADD CONSTRAINT fk_rfq_bid_currency FOREIGN KEY (bid_currency_code) REFERENCES currencies(code),
  ADD CONSTRAINT fk_rfq_invoice_currency FOREIGN KEY (invoice_currency_code) REFERENCES currencies(code);

ALTER TABLE rfq_items
  ADD COLUMN item_type VARCHAR(30) NULL,
  ADD COLUMN item_code VARCHAR(100) NULL,
  ADD COLUMN item_category VARCHAR(100) NULL,
  ADD COLUMN manufacturer VARCHAR(100) NULL,
  ADD COLUMN estimated_unit_price DECIMAL(18,4) NULL,
  ADD COLUMN line_total DECIMAL(18,2) NULL,
  ADD COLUMN price_breaks TEXT NULL,
  ADD COLUMN discount_pct DECIMAL(6,3) NULL,
  ADD COLUMN discount_amount DECIMAL(18,2) NULL,
  ADD COLUMN delivery_date DATE NULL,
  ADD COLUMN lead_time_days INT NULL,
  ADD COLUMN partial_delivery_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN vat_applicable TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN withholding_tax_rate DECIMAL(6,3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN gl_account_id BIGINT UNSIGNED NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN asset_no VARCHAR(100) NULL,
  ADD COLUMN country_of_origin VARCHAR(100) NULL,
  ADD COLUMN hs_code VARCHAR(50) NULL,
  ADD COLUMN warranty_period VARCHAR(50) NULL,
  ADD COLUMN compliance_cert_required TINYINT NOT NULL DEFAULT 0;

ALTER TABLE rfq_items
  ADD CONSTRAINT fk_rfqitem_gl FOREIGN KEY (gl_account_id) REFERENCES accounts(id),
  ADD CONSTRAINT fk_rfqitem_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

ALTER TABLE rfq_vendor_quotes
  ADD COLUMN quotation_currency_code CHAR(3) NULL,
  ADD COLUMN quotation_validity DATE NULL,
  ADD COLUMN delivery_lead_time_days INT NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN deviation_notes TEXT NULL,
  ADD COLUMN attachment_path VARCHAR(255) NULL,
  ADD COLUMN price_score DECIMAL(6,3) NULL,
  ADD COLUMN delivery_score DECIMAL(6,3) NULL,
  ADD COLUMN payment_term_score DECIMAL(6,3) NULL,
  ADD COLUMN total_score DECIMAL(6,3) NULL,
  ADD COLUMN rank_no INT NULL,
  ADD COLUMN technical_compliance TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN qa_approval TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN engineering_approval TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN award_status VARCHAR(20) NULL,
  ADD COLUMN award_date DATE NULL,
  ADD COLUMN award_notes TEXT NULL;

ALTER TABLE rfq_vendor_quotes
  ADD CONSTRAINT fk_rfqquote_currency FOREIGN KEY (quotation_currency_code) REFERENCES currencies(code);

ALTER TABLE rfq_vendor_quote_items
  ADD COLUMN quoted_qty DECIMAL(18,4) NULL,
  ADD COLUMN quoted_unit_price DECIMAL(18,4) NULL,
  ADD COLUMN quoted_total DECIMAL(18,2) NULL;

ALTER TABLE purchase_orders
  ADD COLUMN source_doc_type VARCHAR(20) NULL,
  ADD COLUMN source_rfq_id BIGINT UNSIGNED NULL,
  ADD COLUMN source_rfq_no VARCHAR(50) NULL,
  ADD COLUMN po_type VARCHAR(30) NULL,
  ADD COLUMN price_control_type VARCHAR(30) NULL,
  ADD COLUMN delivery_address_id VARCHAR(100) NULL,
  ADD COLUMN invoice_matching_type VARCHAR(30) NULL,
  ADD COLUMN acceptance_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN receipt_required TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN service_acceptance_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN inspection_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN tolerance_qty_pct DECIMAL(6,3) NULL,
  ADD COLUMN tolerance_price_pct DECIMAL(6,3) NULL,
  ADD COLUMN over_delivery_pct DECIMAL(6,3) NULL,
  ADD COLUMN invoice_currency_code CHAR(3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN withholding_tax_pct DECIMAL(6,3) NULL,
  ADD COLUMN invoice_matching_rule VARCHAR(50) NULL,
  ADD COLUMN retention_pct DECIMAL(6,3) NULL,
  ADD COLUMN milestone_billing TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN advance_payment_pct DECIMAL(6,3) NULL,
  ADD COLUMN agreed_payment_terms VARCHAR(100) NULL,
  ADD COLUMN agreed_delivery_date DATE NULL;

ALTER TABLE purchase_orders
  ADD CONSTRAINT fk_po_source_rfq FOREIGN KEY (source_rfq_id) REFERENCES rfqs(id),
  ADD CONSTRAINT fk_po_invoice_currency FOREIGN KEY (invoice_currency_code) REFERENCES currencies(code);

ALTER TABLE purchase_order_items
  ADD COLUMN source_rfq_item_id BIGINT UNSIGNED NULL,
  ADD COLUMN final_unit_price DECIMAL(18,4) NULL,
  ADD COLUMN final_qty DECIMAL(18,4) NULL,
  ADD COLUMN final_currency_code CHAR(3) NULL,
  ADD COLUMN agreed_payment_terms VARCHAR(100) NULL,
  ADD COLUMN agreed_delivery_date DATE NULL;

ALTER TABLE purchase_order_items
  ADD CONSTRAINT fk_poi_source_rfq_item FOREIGN KEY (source_rfq_item_id) REFERENCES rfq_items(id),
  ADD CONSTRAINT fk_poi_final_currency FOREIGN KEY (final_currency_code) REFERENCES currencies(code);
-- Sales expansion
ALTER TABLE sales_quotations
  ADD COLUMN document_type VARCHAR(30) NULL,
  ADD COLUMN document_status VARCHAR(30) NULL,
  ADD COLUMN version_no VARCHAR(20) NULL,
  ADD COLUMN reference_doc_no VARCHAR(50) NULL,
  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,
  ADD COLUMN customer_group VARCHAR(100) NULL,
  ADD COLUMN bill_to_party VARCHAR(150) NULL,
  ADD COLUMN ship_to_party VARCHAR(150) NULL,
  ADD COLUMN payer VARCHAR(150) NULL,
  ADD COLUMN end_customer VARCHAR(150) NULL,
  ADD COLUMN customer_vat_number VARCHAR(50) NULL,
  ADD COLUMN customer_cr_number VARCHAR(50) NULL,
  ADD COLUMN customer_national_address_id VARCHAR(100) NULL,
  ADD COLUMN quotation_validity_date DATE NULL,
  ADD COLUMN order_date DATE NULL,
  ADD COLUMN requested_delivery_date DATE NULL,
  ADD COLUMN confirmed_delivery_date DATE NULL,
  ADD COLUMN billing_date DATE NULL,
  ADD COLUMN price_list VARCHAR(100) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_limit DECIMAL(18,2) NULL,
  ADD COLUMN credit_check_status VARCHAR(50) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN delivery_terms VARCHAR(50) NULL,
  ADD COLUMN salesperson_id BIGINT UNSIGNED NULL,
  ADD COLUMN tax_scheme VARCHAR(50) NULL,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN withholding_tax_rate DECIMAL(6,3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN zatca_compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN shipping_method VARCHAR(50) NULL,
  ADD COLUMN delivery_priority VARCHAR(50) NULL,
  ADD COLUMN partial_delivery_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN packing_instructions TEXT NULL,
  ADD COLUMN header_notes TEXT NULL,
  ADD COLUMN internal_notes TEXT NULL,
  ADD COLUMN attachments_ref VARCHAR(150) NULL,
  ADD COLUMN open_quantity DECIMAL(18,4) NULL,
  ADD COLUMN open_amount DECIMAL(18,2) NULL,
  ADD COLUMN blocked_status VARCHAR(20) NULL,
  ADD COLUMN cancellation_reason VARCHAR(150) NULL;

ALTER TABLE sales_quotations
  ADD CONSTRAINT fk_sq_salesperson FOREIGN KEY (salesperson_id) REFERENCES users(id),
  ADD CONSTRAINT fk_sq_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE sales_quotation_items
  ADD COLUMN item_type VARCHAR(30) NULL,
  ADD COLUMN item_category VARCHAR(100) NULL,
  ADD COLUMN brand VARCHAR(100) NULL,
  ADD COLUMN delivered_qty DECIMAL(18,4) NULL,
  ADD COLUMN invoiced_qty DECIMAL(18,4) NULL,
  ADD COLUMN line_net_amount DECIMAL(18,2) NULL,
  ADD COLUMN line_gross_amount DECIMAL(18,2) NULL,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN delivery_date DATE NULL,
  ADD COLUMN shipping_point VARCHAR(100) NULL,
  ADD COLUMN batch_no VARCHAR(100) NULL,
  ADD COLUMN serial_no VARCHAR(100) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN revenue_account_id BIGINT UNSIGNED NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN profit_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN price_override_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approval_required_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN backorder_qty DECIMAL(18,4) NULL;

ALTER TABLE sales_quotation_items
  ADD CONSTRAINT fk_sqi_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  ADD CONSTRAINT fk_sqi_revenue_account FOREIGN KEY (revenue_account_id) REFERENCES accounts(id),
  ADD CONSTRAINT fk_sqi_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

ALTER TABLE sales_orders
  ADD COLUMN document_type VARCHAR(30) NULL,
  ADD COLUMN document_status VARCHAR(30) NULL,
  ADD COLUMN version_no VARCHAR(20) NULL,
  ADD COLUMN reference_doc_no VARCHAR(50) NULL,
  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,
  ADD COLUMN customer_group VARCHAR(100) NULL,
  ADD COLUMN bill_to_party VARCHAR(150) NULL,
  ADD COLUMN ship_to_party VARCHAR(150) NULL,
  ADD COLUMN payer VARCHAR(150) NULL,
  ADD COLUMN end_customer VARCHAR(150) NULL,
  ADD COLUMN customer_vat_number VARCHAR(50) NULL,
  ADD COLUMN customer_cr_number VARCHAR(50) NULL,
  ADD COLUMN customer_national_address_id VARCHAR(100) NULL,
  ADD COLUMN quotation_validity_date DATE NULL,
  ADD COLUMN requested_delivery_date DATE NULL,
  ADD COLUMN confirmed_delivery_date DATE NULL,
  ADD COLUMN billing_date DATE NULL,
  ADD COLUMN price_list VARCHAR(100) NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_limit DECIMAL(18,2) NULL,
  ADD COLUMN credit_check_status VARCHAR(50) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN delivery_terms VARCHAR(50) NULL,
  ADD COLUMN salesperson_id BIGINT UNSIGNED NULL,
  ADD COLUMN tax_scheme VARCHAR(50) NULL,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN withholding_tax_rate DECIMAL(6,3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN zatca_compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN shipping_method VARCHAR(50) NULL,
  ADD COLUMN delivery_priority VARCHAR(50) NULL,
  ADD COLUMN partial_delivery_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN packing_instructions TEXT NULL,
  ADD COLUMN header_notes TEXT NULL,
  ADD COLUMN internal_notes TEXT NULL,
  ADD COLUMN attachments_ref VARCHAR(150) NULL,
  ADD COLUMN open_quantity DECIMAL(18,4) NULL,
  ADD COLUMN open_amount DECIMAL(18,2) NULL,
  ADD COLUMN blocked_status VARCHAR(20) NULL,
  ADD COLUMN cancellation_reason VARCHAR(150) NULL;

ALTER TABLE sales_orders
  ADD CONSTRAINT fk_so_salesperson FOREIGN KEY (salesperson_id) REFERENCES users(id),
  ADD CONSTRAINT fk_so_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE sales_order_items
  ADD COLUMN item_type VARCHAR(30) NULL,
  ADD COLUMN item_category VARCHAR(100) NULL,
  ADD COLUMN brand VARCHAR(100) NULL,
  ADD COLUMN invoiced_qty DECIMAL(18,4) NULL,
  ADD COLUMN line_net_amount DECIMAL(18,2) NULL,
  ADD COLUMN line_gross_amount DECIMAL(18,2) NULL,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN delivery_date DATE NULL,
  ADD COLUMN shipping_point VARCHAR(100) NULL,
  ADD COLUMN batch_no VARCHAR(100) NULL,
  ADD COLUMN serial_no VARCHAR(100) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN revenue_account_id BIGINT UNSIGNED NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN profit_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN price_override_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approval_required_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN backorder_qty DECIMAL(18,4) NULL;

ALTER TABLE sales_order_items
  ADD CONSTRAINT fk_soi_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
  ADD CONSTRAINT fk_soi_revenue_account FOREIGN KEY (revenue_account_id) REFERENCES accounts(id),
  ADD CONSTRAINT fk_soi_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

ALTER TABLE delivery_notes
  ADD COLUMN delivery_status VARCHAR(20) NULL,
  ADD COLUMN pick_status VARCHAR(20) NULL,
  ADD COLUMN pack_status VARCHAR(20) NULL,
  ADD COLUMN goods_issue_date DATE NULL,
  ADD COLUMN carrier VARCHAR(100) NULL,
  ADD COLUMN tracking_number VARCHAR(100) NULL,
  ADD COLUMN shipping_method VARCHAR(50) NULL,
  ADD COLUMN delivery_priority VARCHAR(50) NULL,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL;

ALTER TABLE delivery_notes
  ADD CONSTRAINT fk_dn_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE delivery_note_items
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN shipping_point VARCHAR(100) NULL,
  ADD COLUMN batch_no VARCHAR(100) NULL,
  ADD COLUMN serial_no VARCHAR(100) NULL;

ALTER TABLE delivery_note_items
  ADD CONSTRAINT fk_dni_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE invoices
  ADD COLUMN document_type VARCHAR(30) NULL,
  ADD COLUMN document_status VARCHAR(30) NULL,
  ADD COLUMN version_no VARCHAR(20) NULL,
  ADD COLUMN reference_doc_no VARCHAR(50) NULL,
  ADD COLUMN bill_to_party VARCHAR(150) NULL,
  ADD COLUMN ship_to_party VARCHAR(150) NULL,
  ADD COLUMN payer VARCHAR(150) NULL,
  ADD COLUMN end_customer VARCHAR(150) NULL,
  ADD COLUMN due_date DATE NULL,
  ADD COLUMN payment_terms VARCHAR(100) NULL,
  ADD COLUMN credit_limit DECIMAL(18,2) NULL,
  ADD COLUMN credit_check_status VARCHAR(50) NULL,
  ADD COLUMN incoterms VARCHAR(50) NULL,
  ADD COLUMN delivery_terms VARCHAR(50) NULL,
  ADD COLUMN salesperson_id BIGINT UNSIGNED NULL,
  ADD COLUMN tax_scheme VARCHAR(50) NULL,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN withholding_tax_rate DECIMAL(6,3) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN zatca_compliance_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN warehouse_id BIGINT UNSIGNED NULL,
  ADD COLUMN shipping_method VARCHAR(50) NULL,
  ADD COLUMN delivery_priority VARCHAR(50) NULL,
  ADD COLUMN partial_delivery_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN packing_instructions TEXT NULL,
  ADD COLUMN header_notes TEXT NULL,
  ADD COLUMN internal_notes TEXT NULL,
  ADD COLUMN open_amount DECIMAL(18,2) NULL,
  ADD COLUMN blocked_status VARCHAR(20) NULL,
  ADD COLUMN cancellation_reason VARCHAR(150) NULL,
  ADD COLUMN invoice_type VARCHAR(30) NULL;

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 invoices
  ADD CONSTRAINT fk_inv_salesperson FOREIGN KEY (salesperson_id) REFERENCES users(id),
  ADD CONSTRAINT fk_inv_warehouse FOREIGN KEY (warehouse_id) REFERENCES warehouses(id);

ALTER TABLE invoice_items
  ADD COLUMN item_type VARCHAR(30) NULL,
  ADD COLUMN item_category VARCHAR(100) NULL,
  ADD COLUMN brand VARCHAR(100) NULL,
  ADD COLUMN line_net_amount DECIMAL(18,2) NULL,
  ADD COLUMN line_gross_amount DECIMAL(18,2) NULL,
  ADD COLUMN vat_amount DECIMAL(18,2) NULL,
  ADD COLUMN tax_code VARCHAR(50) NULL,
  ADD COLUMN revenue_account_id BIGINT UNSIGNED NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN profit_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN price_override_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approval_required_flag TINYINT NOT NULL DEFAULT 0;

ALTER TABLE invoice_items
  ADD CONSTRAINT fk_invi_revenue_account FOREIGN KEY (revenue_account_id) REFERENCES accounts(id),
  ADD CONSTRAINT fk_invi_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

CREATE TABLE IF NOT EXISTS sales_pricing_conditions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(30) NOT NULL,
  document_id BIGINT UNSIGNED NOT NULL,
  line_id BIGINT UNSIGNED NULL,
  condition_type VARCHAR(50) NOT NULL,
  amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_spc_company FOREIGN KEY (company_id) REFERENCES companies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- HR expansion
ALTER TABLE employees
  ADD COLUMN employee_number VARCHAR(50) NULL,
  ADD COLUMN employee_status VARCHAR(20) NULL,
  ADD COLUMN employment_type VARCHAR(30) NULL,
  ADD COLUMN employee_category VARCHAR(30) NULL,
  ADD COLUMN business_unit VARCHAR(100) NULL,
  ADD COLUMN position_title VARCHAR(150) NULL,
  ADD COLUMN grade_level VARCHAR(50) NULL,
  ADD COLUMN reporting_manager_id BIGINT UNSIGNED NULL,
  ADD COLUMN confirmation_date DATE NULL,
  ADD COLUMN contract_start_date DATE NULL,
  ADD COLUMN contract_end_date DATE NULL,
  ADD COLUMN first_name VARCHAR(100) NULL,
  ADD COLUMN middle_name VARCHAR(100) NULL,
  ADD COLUMN last_name VARCHAR(100) NULL,
  ADD COLUMN full_name VARCHAR(200) NULL,
  ADD COLUMN gender VARCHAR(20) NULL,
  ADD COLUMN date_of_birth DATE NULL,
  ADD COLUMN nationality VARCHAR(100) NULL,
  ADD COLUMN marital_status VARCHAR(50) NULL,
  ADD COLUMN religion VARCHAR(50) NULL,
  ADD COLUMN blood_group VARCHAR(20) NULL,
  ADD COLUMN iqama_expiry_date DATE NULL,
  ADD COLUMN passport_number VARCHAR(50) NULL,
  ADD COLUMN passport_expiry_date DATE NULL,
  ADD COLUMN passport_issuing_country VARCHAR(100) NULL,
  ADD COLUMN visa_type VARCHAR(50) NULL,
  ADD COLUMN visa_expiry_date DATE NULL,
  ADD COLUMN personal_mobile VARCHAR(50) NULL,
  ADD COLUMN work_mobile VARCHAR(50) NULL,
  ADD COLUMN personal_email VARCHAR(150) NULL,
  ADD COLUMN work_email VARCHAR(150) NULL,
  ADD COLUMN emergency_contact_name VARCHAR(150) NULL,
  ADD COLUMN emergency_contact_relation VARCHAR(50) NULL,
  ADD COLUMN emergency_contact_phone VARCHAR(50) NULL,
  ADD COLUMN national_address TEXT NULL,
  ADD COLUMN address_line1 VARCHAR(150) NULL,
  ADD COLUMN city VARCHAR(100) NULL,
  ADD COLUMN state_province VARCHAR(100) NULL,
  ADD COLUMN postal_code VARCHAR(20) NULL,
  ADD COLUMN country VARCHAR(100) NULL,
  ADD COLUMN data_access_level VARCHAR(20) NULL,
  ADD COLUMN record_status VARCHAR(20) NULL,
  ADD COLUMN created_by BIGINT UNSIGNED NULL,
  ADD COLUMN updated_by BIGINT UNSIGNED NULL,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL;

ALTER TABLE employees
  ADD CONSTRAINT fk_emp_reporting_manager FOREIGN KEY (reporting_manager_id) REFERENCES employees(id);

CREATE TABLE IF NOT EXISTS employee_salary_structures (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id BIGINT UNSIGNED NOT NULL,
  salary_type VARCHAR(20) NOT NULL DEFAULT 'monthly',
  basic_salary DECIMAL(18,2) NOT NULL DEFAULT 0,
  housing_allowance DECIMAL(18,2) NOT NULL DEFAULT 0,
  transport_allowance DECIMAL(18,2) NOT NULL DEFAULT 0,
  other_allowances DECIMAL(18,2) NOT NULL DEFAULT 0,
  gross_salary DECIMAL(18,2) NOT NULL DEFAULT 0,
  gosi_employee_pct DECIMAL(6,3) NULL,
  gosi_employer_pct DECIMAL(6,3) NULL,
  tax_deduction DECIMAL(18,2) NOT NULL DEFAULT 0,
  loan_deduction DECIMAL(18,2) NOT NULL DEFAULT 0,
  advance_deduction DECIMAL(18,2) NOT NULL DEFAULT 0,
  other_deduction DECIMAL(18,2) NOT NULL DEFAULT 0,
  net_salary DECIMAL(18,2) NOT NULL DEFAULT 0,
  effective_from DATE NULL,
  effective_to DATE NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_ess_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE payroll_runs
  ADD COLUMN period_month INT NULL,
  ADD COLUMN period_year INT NULL,
  ADD COLUMN posting_reference VARCHAR(100) NULL,
  ADD COLUMN payment_method VARCHAR(50) NULL,
  ADD COLUMN bank_file_reference VARCHAR(100) NULL;

ALTER TABLE payroll_items
  ADD COLUMN basic_salary DECIMAL(18,2) NULL,
  ADD COLUMN allowances DECIMAL(18,2) NULL,
  ADD COLUMN overtime_amount DECIMAL(18,2) NULL,
  ADD COLUMN bonus_amount DECIMAL(18,2) NULL,
  ADD COLUMN deductions DECIMAL(18,2) NULL,
  ADD COLUMN net_pay DECIMAL(18,2) NULL,
  ADD COLUMN payroll_currency CHAR(3) NULL,
  ADD COLUMN exchange_rate DECIMAL(18,6) NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN gl_posting_reference VARCHAR(100) NULL,
  ADD COLUMN payment_method VARCHAR(50) NULL;

ALTER TABLE payroll_items
  ADD CONSTRAINT fk_pi_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id),
  ADD CONSTRAINT fk_pi_currency FOREIGN KEY (payroll_currency) REFERENCES currencies(code);

ALTER TABLE leave_types
  ADD COLUMN leave_category VARCHAR(50) NULL,
  ADD COLUMN max_allowed_days INT NULL,
  ADD COLUMN carry_forward_allowed TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN paid_flag TINYINT NOT NULL DEFAULT 1;

CREATE TABLE IF NOT EXISTS leave_balances (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id BIGINT UNSIGNED NOT NULL,
  leave_type_id BIGINT UNSIGNED NOT NULL,
  opening_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
  accrued_days DECIMAL(18,2) NOT NULL DEFAULT 0,
  used_days DECIMAL(18,2) NOT NULL DEFAULT 0,
  remaining_balance DECIMAL(18,2) NOT NULL DEFAULT 0,
  updated_at DATETIME NULL,
  CONSTRAINT fk_lb_emp FOREIGN KEY (employee_id) REFERENCES employees(id),
  CONSTRAINT fk_lb_type FOREIGN KEY (leave_type_id) REFERENCES leave_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE leave_requests
  ADD COLUMN total_days DECIMAL(18,2) NULL,
  ADD COLUMN reason VARCHAR(255) NULL,
  ADD COLUMN approver_id BIGINT UNSIGNED NULL,
  ADD COLUMN approved_date DATE NULL;

ALTER TABLE leave_requests
  ADD CONSTRAINT fk_lr_approver FOREIGN KEY (approver_id) REFERENCES users(id);

ALTER TABLE employee_travel
  ADD COLUMN travel_type VARCHAR(20) NULL,
  ADD COLUMN from_location VARCHAR(150) NULL,
  ADD COLUMN to_location VARCHAR(150) NULL,
  ADD COLUMN status VARCHAR(20) NULL;

CREATE TABLE IF NOT EXISTS employee_travel_expenses (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  travel_id BIGINT UNSIGNED NOT NULL,
  expense_type VARCHAR(50) NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency_code CHAR(3) NULL,
  exchange_rate DECIMAL(18,6) NULL,
  receipt_attachment_id BIGINT UNSIGNED NULL,
  approved_amount DECIMAL(18,2) NULL,
  CONSTRAINT fk_ete_travel FOREIGN KEY (travel_id) REFERENCES employee_travel(id),
  CONSTRAINT fk_ete_currency FOREIGN KEY (currency_code) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS employee_travel_advances (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  travel_id BIGINT UNSIGNED NOT NULL,
  advance_amount DECIMAL(18,2) NOT NULL DEFAULT 0,
  advance_currency CHAR(3) NULL,
  settlement_status VARCHAR(20) NULL,
  CONSTRAINT fk_eta_travel FOREIGN KEY (travel_id) REFERENCES employee_travel(id),
  CONSTRAINT fk_eta_currency FOREIGN KEY (advance_currency) REFERENCES currencies(code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE employee_documents
  ADD COLUMN document_number VARCHAR(100) NULL,
  ADD COLUMN issue_date DATE NULL,
  ADD COLUMN expiry_date DATE NULL,
  ADD COLUMN confidentiality_level VARCHAR(50) NULL,
  ADD COLUMN verified_flag TINYINT NOT NULL DEFAULT 0;

CREATE TABLE IF NOT EXISTS hr_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  employee_id BIGINT UNSIGNED NOT NULL,
  request_type VARCHAR(50) NOT NULL,
  request_date DATE NOT NULL,
  effective_date DATE NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  approval_workflow_id VARCHAR(50) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_hr_req_emp FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Fleet expansion
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;

CREATE TABLE IF NOT EXISTS vehicle_maintenance_plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id BIGINT UNSIGNED NOT NULL,
  maintenance_type VARCHAR(50) NOT NULL,
  frequency_type VARCHAR(20) NOT NULL,
  interval_value INT NOT NULL,
  last_service_date DATE NULL,
  last_service_odometer INT NULL,
  next_due_date DATE NULL,
  next_due_odometer INT NULL,
  CONSTRAINT fk_vmp_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE vehicle_maintenance
  ADD COLUMN maintenance_category VARCHAR(50) NULL,
  ADD COLUMN workshop_type VARCHAR(30) NULL,
  ADD COLUMN vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN start_date DATE NULL,
  ADD COLUMN end_date DATE NULL,
  ADD COLUMN downtime_hours INT NULL,
  ADD COLUMN status VARCHAR(20) NULL,
  ADD COLUMN labor_cost DECIMAL(18,2) NULL,
  ADD COLUMN parts_cost DECIMAL(18,2) NULL,
  ADD COLUMN external_service_cost DECIMAL(18,2) NULL,
  ADD COLUMN total_cost DECIMAL(18,2) NULL,
  ADD COLUMN currency_code CHAR(3) NULL;

ALTER TABLE vehicle_maintenance
  ADD CONSTRAINT fk_vm_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_vm_currency FOREIGN KEY (currency_code) REFERENCES currencies(code);

ALTER TABLE vehicle_warranties
  ADD COLUMN coverage_description VARCHAR(255) NULL,
  ADD COLUMN coverage_limit_km INT NULL,
  ADD COLUMN remaining_coverage_km INT NULL,
  ADD COLUMN warranty_provider VARCHAR(150) NULL,
  ADD COLUMN claim_procedure_ref VARCHAR(150) NULL;

CREATE TABLE IF NOT EXISTS vehicle_warranty_claims (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  warranty_id BIGINT UNSIGNED NOT NULL,
  maintenance_id BIGINT UNSIGNED NULL,
  claim_date DATE NULL,
  claimed_amount DECIMAL(18,2) NULL,
  approved_amount DECIMAL(18,2) NULL,
  claim_status VARCHAR(20) NULL,
  CONSTRAINT fk_vwc_warranty FOREIGN KEY (warranty_id) REFERENCES vehicle_warranties(id),
  CONSTRAINT fk_vwc_maintenance FOREIGN KEY (maintenance_id) REFERENCES vehicle_maintenance(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE vehicle_expenses
  ADD COLUMN expense_type VARCHAR(50) NULL,
  ADD COLUMN expense_category VARCHAR(50) NULL,
  ADD COLUMN vendor_id BIGINT UNSIGNED NULL,
  ADD COLUMN payment_method VARCHAR(30) NULL,
  ADD COLUMN currency_code CHAR(3) NULL,
  ADD COLUMN quantity DECIMAL(18,2) NULL,
  ADD COLUMN unit_cost DECIMAL(18,4) NULL,
  ADD COLUMN tax_amount DECIMAL(18,2) NULL,
  ADD COLUMN vat_applicable TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN vat_rate DECIMAL(6,3) NULL,
  ADD COLUMN invoice_reference VARCHAR(100) NULL,
  ADD COLUMN cost_center_id BIGINT UNSIGNED NULL,
  ADD COLUMN project_code VARCHAR(100) NULL,
  ADD COLUMN posting_status VARCHAR(20) NULL;

ALTER TABLE vehicle_expenses
  ADD CONSTRAINT fk_ve_vendor FOREIGN KEY (vendor_id) REFERENCES vendors(id),
  ADD CONSTRAINT fk_ve_currency FOREIGN KEY (currency_code) REFERENCES currencies(code),
  ADD CONSTRAINT fk_ve_cost_center FOREIGN KEY (cost_center_id) REFERENCES cost_centers(id);

ALTER TABLE vehicle_reminders
  ADD COLUMN reference_type VARCHAR(50) NULL,
  ADD COLUMN reference_id BIGINT UNSIGNED NULL,
  ADD COLUMN trigger_condition VARCHAR(30) NULL,
  ADD COLUMN trigger_value VARCHAR(50) NULL,
  ADD COLUMN reminder_frequency VARCHAR(30) NULL,
  ADD COLUMN notification_channel VARCHAR(30) NULL,
  ADD COLUMN assigned_to_user_id BIGINT UNSIGNED NULL,
  ADD COLUMN last_triggered_date DATE NULL,
  ADD COLUMN next_trigger_date DATE NULL;

ALTER TABLE vehicle_reminders
  ADD CONSTRAINT fk_vr_assigned_user FOREIGN KEY (assigned_to_user_id) REFERENCES users(id);

CREATE TABLE IF NOT EXISTS vehicle_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id BIGINT UNSIGNED NOT NULL,
  document_type VARCHAR(50) NOT NULL,
  document_number VARCHAR(100) NULL,
  issue_date DATE NULL,
  expiry_date DATE NULL,
  issuing_authority VARCHAR(150) NULL,
  attachment_id BIGINT UNSIGNED NULL,
  confidentiality_level VARCHAR(50) NULL,
  verified_flag TINYINT NOT NULL DEFAULT 0,
  CONSTRAINT fk_vd_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
  CONSTRAINT fk_vd_attachment FOREIGN KEY (attachment_id) REFERENCES attachments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS vehicle_incidents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  vehicle_id BIGINT UNSIGNED NOT NULL,
  driver_id BIGINT UNSIGNED NULL,
  incident_date DATE NULL,
  location VARCHAR(150) NULL,
  incident_type VARCHAR(50) NULL,
  description TEXT NULL,
  insurance_claim_reference VARCHAR(100) NULL,
  repair_cost DECIMAL(18,2) NULL,
  status VARCHAR(20) NULL,
  CONSTRAINT fk_vi_vehicle FOREIGN KEY (vehicle_id) REFERENCES vehicles(id),
  CONSTRAINT fk_vi_driver FOREIGN KEY (driver_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Attachments expansion
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 version_no INT NOT NULL DEFAULT 1,
  ADD COLUMN is_latest TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN upload_source VARCHAR(30) NULL,
  ADD COLUMN storage_type VARCHAR(30) NULL,
  ADD COLUMN storage_provider VARCHAR(50) NULL,
  ADD COLUMN storage_key VARCHAR(255) NULL,
  ADD COLUMN encryption_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN compression_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN access_level VARCHAR(30) NULL,
  ADD COLUMN allowed_roles TEXT NULL,
  ADD COLUMN allowed_users TEXT NULL,
  ADD COLUMN download_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN print_allowed TINYINT NOT NULL DEFAULT 1,
  ADD COLUMN watermark_enabled TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN confidentiality_level VARCHAR(30) NULL,
  ADD COLUMN contains_pii TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN data_classification VARCHAR(50) NULL,
  ADD COLUMN legal_hold_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN retention_policy_id BIGINT UNSIGNED NULL,
  ADD COLUMN retention_expiry_date DATE NULL,
  ADD COLUMN document_status VARCHAR(20) NULL,
  ADD COLUMN verified_by BIGINT UNSIGNED NULL,
  ADD COLUMN verified_date DATE NULL,
  ADD COLUMN approval_required TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN approved_by BIGINT UNSIGNED NULL,
  ADD COLUMN approval_date DATE NULL,
  ADD COLUMN expiry_date DATE NULL,
  ADD COLUMN auto_archive_flag TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN parent_document_id BIGINT UNSIGNED NULL,
  ADD COLUMN change_reason VARCHAR(150) NULL,
  ADD COLUMN superseded_date DATE NULL,
  ADD COLUMN superseded_by BIGINT UNSIGNED NULL,
  ADD COLUMN version_notes TEXT NULL,
  ADD COLUMN tags TEXT NULL,
  ADD COLUMN ocr_enabled TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN ocr_text LONGTEXT NULL,
  ADD COLUMN language VARCHAR(30) NULL,
  ADD COLUMN search_index_status VARCHAR(20) NULL,
  ADD COLUMN last_accessed_by BIGINT UNSIGNED NULL,
  ADD COLUMN last_accessed_at DATETIME NULL,
  ADD COLUMN download_count INT NOT NULL DEFAULT 0,
  ADD COLUMN access_log_id BIGINT UNSIGNED NULL,
  ADD COLUMN ip_address VARCHAR(50) NULL,
  ADD COLUMN reminder_enabled TINYINT NOT NULL DEFAULT 0,
  ADD COLUMN reminder_type VARCHAR(30) NULL,
  ADD COLUMN reminder_schedule VARCHAR(100) NULL,
  ADD COLUMN notified_roles TEXT NULL,
  ADD COLUMN notified_users TEXT NULL,
  ADD COLUMN source_system VARCHAR(50) NULL,
  ADD COLUMN external_reference_id VARCHAR(100) NULL,
  ADD COLUMN api_sync_status VARCHAR(20) NULL;

ALTER TABLE attachments
  ADD CONSTRAINT fk_att_verified_by FOREIGN KEY (verified_by) REFERENCES users(id),
  ADD CONSTRAINT fk_att_approved_by FOREIGN KEY (approved_by) REFERENCES users(id),
  ADD CONSTRAINT fk_att_parent FOREIGN KEY (parent_document_id) REFERENCES attachments(id),
  ADD CONSTRAINT fk_att_superseded_by FOREIGN KEY (superseded_by) REFERENCES users(id);
