CREATE DATABASE IF NOT EXISTS she_chantier
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE she_chantier;

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS validation_items;
DROP TABLE IF EXISTS validation_batches;
DROP TABLE IF EXISTS documents;
DROP TABLE IF EXISTS document_types;
DROP TABLE IF EXISTS delivery_lines;
DROP TABLE IF EXISTS deliveries;
DROP TABLE IF EXISTS purchase_order_lines;
DROP TABLE IF EXISTS purchase_orders;
DROP TABLE IF EXISTS quote_validations;
DROP TABLE IF EXISTS supplier_quote_lines;
DROP TABLE IF EXISTS supplier_quotes;
DROP TABLE IF EXISTS purchase_request_items;
DROP TABLE IF EXISTS purchase_requests;
DROP TABLE IF EXISTS stock_movements;
DROP TABLE IF EXISTS stock_items;
DROP TABLE IF EXISTS third_party_payments;
DROP TABLE IF EXISTS third_party_dues;
DROP TABLE IF EXISTS treasury_reconciliations;
DROP TABLE IF EXISTS cash_transactions;
DROP TABLE IF EXISTS fuel_operations;
DROP TABLE IF EXISTS cash_accounts;
DROP TABLE IF EXISTS asset_maintenance;
DROP TABLE IF EXISTS asset_payments;
DROP TABLE IF EXISTS asset_timesheets;
DROP TABLE IF EXISTS asset_renters;
DROP TABLE IF EXISTS assets;
DROP TABLE IF EXISTS organizations;
DROP TABLE IF EXISTS organization_types;
DROP TABLE IF EXISTS job_functions;
DROP TABLE IF EXISTS project_daily_journals;
DROP TABLE IF EXISTS work_progress_entries;
DROP TABLE IF EXISTS price_items;
DROP TABLE IF EXISTS project_delay_orders;
DROP TABLE IF EXISTS project_contracts;
DROP TABLE IF EXISTS project_assignments;
DROP TABLE IF EXISTS project_statuses;
DROP TABLE IF EXISTS projects;
DROP TABLE IF EXISTS owners;
DROP TABLE IF EXISTS user_project_permissions;
DROP TABLE IF EXISTS role_permissions;
DROP TABLE IF EXISTS permissions;
DROP TABLE IF EXISTS user_role;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS company_settings;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS supplier_categories;
DROP TABLE IF EXISTS employee_timesheets;
DROP TABLE IF EXISTS audit_logs;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(64) NOT NULL UNIQUE,
    label VARCHAR(120) NOT NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(120) NOT NULL UNIQUE,
    label VARCHAR(150) NOT NULL,
    module VARCHAR(80) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(60) NOT NULL UNIQUE,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(140) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    force_password_change TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    is_read_only TINYINT(1) NOT NULL DEFAULT 0,
    last_password_changed_at DATETIME NULL,
    failed_login_attempts INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL
) ENGINE=InnoDB;

CREATE TABLE user_role (
    user_id INT UNSIGNED NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_user_role_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_role_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_role_permissions_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    CONSTRAINT fk_role_permissions_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE company_settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(120) NOT NULL UNIQUE,
    `value` TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE owners (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(160) NOT NULL,
    contact_name VARCHAR(140) NULL,
    phone VARCHAR(40) NULL,
    email VARCHAR(140) NULL,
    tax_id VARCHAR(80) NULL,
    address TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_owners_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_owners_updated_by FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_owners_name (company_name),
    INDEX idx_owners_email (email)
) ENGINE=InnoDB;

CREATE TABLE project_statuses (
    slug VARCHAR(50) PRIMARY KEY,
    label VARCHAR(100) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    position INT UNSIGNED NOT NULL DEFAULT 10
) ENGINE=InnoDB;

CREATE TABLE projects (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_id INT UNSIGNED NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(180) NOT NULL,
    site_address TEXT NULL,
    project_status_slug VARCHAR(50) NOT NULL DEFAULT 'planifie',
    start_date DATE NULL,
    end_date DATE NULL,
    budget_estimate DECIMAL(16,2) NOT NULL DEFAULT 0,
    advancement_pct DECIMAL(5,2) NOT NULL DEFAULT 0,
    longitude DECIMAL(18, 10) NULL,
    latitude DECIMAL(18, 10) NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_projects_owner FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE,
    CONSTRAINT fk_projects_status FOREIGN KEY (project_status_slug) REFERENCES project_statuses(slug),
    CONSTRAINT fk_projects_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_projects_updated_by FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_projects_name (name),
    INDEX idx_projects_status (project_status_slug),
    INDEX idx_projects_dates (start_date, end_date)
) ENGINE=InnoDB;

CREATE TABLE project_assignments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    role_slug VARCHAR(80) NOT NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    CONSTRAINT fk_project_assign_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_assign_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_assign_user2 FOREIGN KEY (created_by) REFERENCES users(id),
    UNIQUE KEY uq_project_user_role (project_id, user_id, role_slug),
    INDEX idx_project_assign_project (project_id),
    INDEX idx_project_assign_user (user_id)
) ENGINE=InnoDB;

CREATE TABLE project_contracts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    reference VARCHAR(120) NOT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    contract_value DECIMAL(16,2) NOT NULL DEFAULT 0,
    status ENUM('actif','cloture','resilie') NOT NULL DEFAULT 'actif',
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_project_contracts_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_contracts_created_by FOREIGN KEY (created_by) REFERENCES users(id),
    UNIQUE KEY uq_contract_project_reference (project_id, reference)
) ENGINE=InnoDB;
CREATE TABLE project_delay_orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    type_os VARCHAR(80) NOT NULL,
    started_at DATETIME NOT NULL,
    ended_at DATETIME NULL,
    reason TEXT NULL,
    comment TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_project_delay_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_delay_user FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_project_delay_project (project_id)
) ENGINE=InnoDB;

CREATE TABLE price_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    label VARCHAR(180) NOT NULL,
    unit VARCHAR(40) NOT NULL DEFAULT 'm2',
    quantity DECIMAL(18,3) NOT NULL DEFAULT 0,
    unit_price DECIMAL(16,2) NOT NULL DEFAULT 0,
    total_price DECIMAL(16,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_price_items_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    INDEX idx_price_items_project (project_id)
) ENGINE=InnoDB;

CREATE TABLE work_progress_entries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    progress_date DATE NOT NULL,
    avance_quantite DECIMAL(16,3) NOT NULL DEFAULT 0,
    commentaire TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_work_progress_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_work_progress_user FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_work_progress_project (project_id, progress_date)
) ENGINE=InnoDB;

CREATE TABLE project_daily_journals (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    journal_date DATE NOT NULL,
    category ENUM('avancement','pointage_personnel','pointage_engins','livraisons','depenses','gasoil') NOT NULL DEFAULT 'avancement',
    description TEXT NULL,
    amount DECIMAL(16,2) NULL DEFAULT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_journal_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_journal_user FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_journal_project_date (project_id, journal_date)
) ENGINE=InnoDB;

CREATE TABLE organization_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(120) NOT NULL,
    slug VARCHAR(100) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE organizations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_type_id INT UNSIGNED NOT NULL,
    name VARCHAR(180) NOT NULL,
    contact_name VARCHAR(140) NULL,
    phone VARCHAR(60) NULL,
    email VARCHAR(140) NULL,
    address TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_organizations_type FOREIGN KEY (organization_type_id) REFERENCES organization_types(id),
    INDEX idx_organizations_name (name),
    INDEX idx_organizations_type (organization_type_id)
) ENGINE=InnoDB;

CREATE TABLE job_functions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(120) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    matricule VARCHAR(80) NOT NULL UNIQUE,
    full_name VARCHAR(160) NOT NULL,
    job_function_id INT UNSIGNED NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(140) NULL,
    hire_date DATE NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_employees_job_function FOREIGN KEY (job_function_id) REFERENCES job_functions(id),
    INDEX idx_employees_name (full_name),
    INDEX idx_employees_function (job_function_id)
) ENGINE=InnoDB;


CREATE TABLE employee_timesheets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    employee_id INT UNSIGNED NOT NULL,
    project_id INT UNSIGNED NOT NULL,
    timesheet_date DATE NOT NULL,
    check_in TIME NULL,
    check_out TIME NULL,
    worked_hours DECIMAL(6,2) NOT NULL DEFAULT 0,
    note TEXT NULL,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_employee_timesheets_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_timesheets_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_employee_timesheets_creator FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_employee_timesheets_updater FOREIGN KEY (updated_by) REFERENCES users(id),
    INDEX idx_employee_timesheets_employee_date (employee_id, timesheet_date),
    INDEX idx_employee_timesheets_project_date (project_id, timesheet_date)
) ENGINE=InnoDB;
CREATE TABLE assets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(80) NOT NULL UNIQUE,
    type VARCHAR(100) NOT NULL,
    status ENUM('actif','maintenance','hors_service','retire') NOT NULL DEFAULT 'actif',
    purchase_date DATE NULL,
    acquisition_cost DECIMAL(16,2) NULL,
    license_plate VARCHAR(50) NULL,
    power VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    INDEX idx_assets_code (code),
    INDEX idx_assets_status (status)
) ENGINE=InnoDB;

CREATE TABLE asset_renters (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_id INT UNSIGNED NOT NULL,
    organization_id INT UNSIGNED NOT NULL,
    rental_start DATE NOT NULL,
    rental_end DATE NULL,
    daily_rate DECIMAL(16,2) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_asset_renters_asset FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
    CONSTRAINT fk_asset_renters_org FOREIGN KEY (organization_id) REFERENCES organizations(id)
) ENGINE=InnoDB;

CREATE TABLE asset_timesheets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_id INT UNSIGNED NOT NULL,
    timesheet_date DATE NOT NULL,
    started_at TIME NULL,
    ended_at TIME NULL,
    operator_id INT UNSIGNED NULL,
    hours_count DECIMAL(6,2) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_asset_timesheet_asset FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
    CONSTRAINT fk_asset_timesheet_operator FOREIGN KEY (operator_id) REFERENCES users(id),
    CONSTRAINT fk_asset_timesheet_creator FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_asset_timesheet_asset_date (asset_id, timesheet_date)
) ENGINE=InnoDB;

CREATE TABLE asset_payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_id INT UNSIGNED NOT NULL,
    project_id INT UNSIGNED NULL,
    amount DECIMAL(16,2) NOT NULL,
    payment_date DATE NOT NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_asset_payments_asset FOREIGN KEY (asset_id) REFERENCES assets(id),
    CONSTRAINT fk_asset_payments_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
    INDEX idx_asset_payments_asset (asset_id)
) ENGINE=InnoDB;

CREATE TABLE asset_maintenance (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    asset_id INT UNSIGNED NOT NULL,
    maintenance_type VARCHAR(120) NOT NULL,
    intervention_date DATE NOT NULL,
    provider_id INT UNSIGNED NULL,
    cost DECIMAL(14,2) DEFAULT 0,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_asset_maintenance_asset FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
    CONSTRAINT fk_asset_maintenance_provider FOREIGN KEY (provider_id) REFERENCES organizations(id) ON DELETE SET NULL,
    INDEX idx_asset_maintenance_asset_date (asset_id, intervention_date)
) ENGINE=InnoDB;
CREATE TABLE supplier_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(140) NOT NULL,
    slug VARCHAR(140) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE suppliers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supplier_category_id INT UNSIGNED NULL,
    company_name VARCHAR(200) NOT NULL,
    contact_name VARCHAR(150) NULL,
    phone VARCHAR(60) NULL,
    email VARCHAR(140) NULL,
    tax_id VARCHAR(80) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_suppliers_category FOREIGN KEY (supplier_category_id) REFERENCES supplier_categories(id),
    INDEX idx_suppliers_name (company_name)
) ENGINE=InnoDB;

CREATE TABLE purchase_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NULL,
    requested_by INT UNSIGNED NULL,
    request_date DATE NOT NULL,
    expected_date DATE NULL,
    reason TEXT NULL,
    approved TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    CONSTRAINT fk_purchase_requests_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
    CONSTRAINT fk_purchase_requests_requester FOREIGN KEY (requested_by) REFERENCES users(id),
    INDEX idx_purchase_requests_date (request_date),
    INDEX idx_purchase_requests_project (project_id)
) ENGINE=InnoDB;

CREATE TABLE purchase_request_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_request_id INT UNSIGNED NOT NULL,
    description VARCHAR(220) NOT NULL,
    quantity DECIMAL(14,3) NOT NULL DEFAULT 1,
    unit VARCHAR(60) NOT NULL DEFAULT 'unite',
    estimated_unit_price DECIMAL(14,2) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_purchase_request_items_request FOREIGN KEY (purchase_request_id) REFERENCES purchase_requests(id) ON DELETE CASCADE,
    INDEX idx_pri_request (purchase_request_id)
) ENGINE=InnoDB;

CREATE TABLE supplier_quotes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_request_id INT UNSIGNED NOT NULL,
    supplier_id INT UNSIGNED NOT NULL,
    quote_ref VARCHAR(100) NULL,
    quote_date DATE NOT NULL,
    status ENUM('brouillon','soumis','valide','rejete') NOT NULL DEFAULT 'brouillon',
    total_amount DECIMAL(16,2) NOT NULL DEFAULT 0,
    valid_until DATE NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_supplier_quotes_request FOREIGN KEY (purchase_request_id) REFERENCES purchase_requests(id) ON DELETE CASCADE,
    CONSTRAINT fk_supplier_quotes_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    INDEX idx_supplier_quotes_status (status),
    INDEX idx_supplier_quotes_supplier (supplier_id)
) ENGINE=InnoDB;

CREATE TABLE supplier_quote_lines (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supplier_quote_id INT UNSIGNED NOT NULL,
    description VARCHAR(220) NOT NULL,
    quantity DECIMAL(14,3) NOT NULL DEFAULT 1,
    unit_price DECIMAL(14,2) NOT NULL DEFAULT 0,
    total_line DECIMAL(16,2) NOT NULL DEFAULT 0,
    CONSTRAINT fk_supplier_quote_lines_quote FOREIGN KEY (supplier_quote_id) REFERENCES supplier_quotes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE quote_validations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supplier_quote_id INT UNSIGNED NOT NULL,
    validated_by INT UNSIGNED NULL,
    validation_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    state ENUM('en_attente','valide','rejete') NOT NULL DEFAULT 'en_attente',
    comment TEXT NULL,
    CONSTRAINT fk_quote_validations_quote FOREIGN KEY (supplier_quote_id) REFERENCES supplier_quotes(id) ON DELETE CASCADE,
    CONSTRAINT fk_quote_validations_validator FOREIGN KEY (validated_by) REFERENCES users(id),
    INDEX idx_quote_validations_quote (supplier_quote_id)
) ENGINE=InnoDB;

CREATE TABLE purchase_orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    supplier_quote_id INT UNSIGNED NOT NULL,
    project_id INT UNSIGNED NULL,
    number VARCHAR(100) NOT NULL UNIQUE,
    order_date DATE NOT NULL,
    status ENUM('brouillon','envoye','recu_partiel','recu_total','annule') NOT NULL DEFAULT 'brouillon',
    total_amount DECIMAL(16,2) NOT NULL DEFAULT 0,
    supplier_reference VARCHAR(150) NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_purchase_orders_quote FOREIGN KEY (supplier_quote_id) REFERENCES supplier_quotes(id),
    CONSTRAINT fk_purchase_orders_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
    CONSTRAINT fk_purchase_orders_creator FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_purchase_orders_project (project_id)
) ENGINE=InnoDB;

CREATE TABLE purchase_order_lines (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_order_id INT UNSIGNED NOT NULL,
    description VARCHAR(250) NOT NULL,
    quantity DECIMAL(14,3) NOT NULL DEFAULT 1,
    unit VARCHAR(60) NOT NULL DEFAULT 'unite',
    unit_price DECIMAL(16,2) NOT NULL DEFAULT 0,
    total_line DECIMAL(16,2) NOT NULL DEFAULT 0,
    CONSTRAINT fk_purchase_order_lines_order FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE deliveries (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    purchase_order_id INT UNSIGNED NOT NULL,
    delivery_date DATE NOT NULL,
    received_by INT UNSIGNED NULL,
    status ENUM('en_cours','complet','rejete') NOT NULL DEFAULT 'en_cours',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_deliveries_order FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_deliveries_user FOREIGN KEY (received_by) REFERENCES users(id),
    INDEX idx_deliveries_order (purchase_order_id)
) ENGINE=InnoDB;

CREATE TABLE delivery_lines (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    delivery_id INT UNSIGNED NOT NULL,
    purchase_order_line_id INT UNSIGNED NOT NULL,
    product_reference VARCHAR(140) NULL,
    description VARCHAR(220) NOT NULL,
    quantity_ordered DECIMAL(14,3) NOT NULL DEFAULT 0,
    quantity_received DECIMAL(14,3) NOT NULL DEFAULT 0,
    CONSTRAINT fk_delivery_lines_order_line FOREIGN KEY (purchase_order_line_id) REFERENCES purchase_order_lines(id) ON DELETE CASCADE,
    CONSTRAINT fk_delivery_lines_delivery FOREIGN KEY (delivery_id) REFERENCES deliveries(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE stock_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NULL,
    sku VARCHAR(120) NOT NULL,
    name VARCHAR(180) NOT NULL,
    unit VARCHAR(60) NOT NULL DEFAULT 'unite',
    quantity DECIMAL(16,3) NOT NULL DEFAULT 0,
    unit_cost DECIMAL(16,2) NOT NULL DEFAULT 0,
    minimum_stock DECIMAL(16,3) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_stock_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
    UNIQUE KEY uq_stock_sku (sku)
) ENGINE=InnoDB;

CREATE TABLE stock_movements (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stock_item_id INT UNSIGNED NOT NULL,
    movement_type ENUM('entree','sortie','ajustement') NOT NULL,
    quantity DECIMAL(16,3) NOT NULL,
    movement_date DATE NOT NULL,
    reference_module VARCHAR(80) NULL,
    reference_id INT UNSIGNED NULL,
    notes TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_stock_mvt_item FOREIGN KEY (stock_item_id) REFERENCES stock_items(id) ON DELETE CASCADE,
    CONSTRAINT fk_stock_mvt_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE cash_accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(140) NOT NULL,
    `type` ENUM('caisse','banque','credit','autre') NOT NULL DEFAULT 'caisse',
    currency CHAR(3) NOT NULL DEFAULT 'MAD',
    balance DECIMAL(16,2) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE cash_transactions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cash_account_id INT UNSIGNED NOT NULL,
    operation_type ENUM('entree','sortie','virement') NOT NULL,
    amount DECIMAL(16,2) NOT NULL,
    reference_module VARCHAR(100) NULL,
    reference_id INT UNSIGNED NULL,
    operation_date DATE NOT NULL,
    note TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_cash_tx_account FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id),
    CONSTRAINT fk_cash_tx_user FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_cash_tx_account_date (cash_account_id, operation_date)
) ENGINE=InnoDB;

CREATE TABLE fuel_operations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NULL,
    asset_id INT UNSIGNED NULL,
    supplier_id INT UNSIGNED NULL,
    operation_date DATE NOT NULL,
    fuel_type ENUM('essence','gasoil','diesel') NOT NULL DEFAULT 'gasoil',
    liters DECIMAL(10,3) NOT NULL,
    price_per_liter DECIMAL(12,2) NOT NULL,
    total_amount DECIMAL(16,2) NOT NULL,
    tank_before DECIMAL(10,3) NULL,
    tank_after DECIMAL(10,3) NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_fuel_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_asset FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    CONSTRAINT fk_fuel_user FOREIGN KEY (created_by) REFERENCES users(id),
    INDEX idx_fuel_project (project_id)
) ENGINE=InnoDB;

CREATE TABLE third_party_dues (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_id INT UNSIGNED NULL,
    supplier_id INT UNSIGNED NULL,
    project_id INT UNSIGNED NULL,
    due_amount DECIMAL(16,2) NOT NULL DEFAULT 0,
    due_type ENUM('fournisseur','transporteur','salaires','intervenants','location_engin') NOT NULL,
    due_status ENUM('ouvert','partiel','solde') NOT NULL DEFAULT 'ouvert',
    due_date DATE NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_due_org FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE SET NULL,
    CONSTRAINT fk_due_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    CONSTRAINT fk_due_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE third_party_payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    third_party_due_id INT UNSIGNED NOT NULL,
    cash_account_id INT UNSIGNED NULL,
    payment_date DATE NOT NULL,
    amount DECIMAL(16,2) NOT NULL,
    payment_type ENUM('virement','cheque','especes','carte') NOT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tpp_due FOREIGN KEY (third_party_due_id) REFERENCES third_party_dues(id),
    CONSTRAINT fk_tpp_account FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id) ON DELETE SET NULL,
    CONSTRAINT fk_tpp_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE treasury_reconciliations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cash_account_id INT UNSIGNED NOT NULL,
    reconciliation_date DATE NOT NULL,
    bank_balance DECIMAL(16,2) NOT NULL,
    system_balance DECIMAL(16,2) NOT NULL,
    variance DECIMAL(16,2) NOT NULL,
    note TEXT NULL,
    created_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_treasury_account FOREIGN KEY (cash_account_id) REFERENCES cash_accounts(id),
    CONSTRAINT fk_treasury_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE document_types (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(120) NOT NULL UNIQUE,
    label VARCHAR(150) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    document_type_id INT UNSIGNED NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    module_name VARCHAR(120) NOT NULL,
    entity_id INT UNSIGNED NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    size_bytes INT UNSIGNED NULL,
    uploaded_by INT UNSIGNED NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_documents_type FOREIGN KEY (document_type_id) REFERENCES document_types(id),
    CONSTRAINT fk_documents_user FOREIGN KEY (uploaded_by) REFERENCES users(id),
    INDEX idx_documents_module (module_name, entity_id),
    INDEX idx_documents_name (stored_name)
) ENGINE=InnoDB;

CREATE TABLE validation_batches (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module_name VARCHAR(120) NOT NULL,
    reference_id INT UNSIGNED NOT NULL,
    status ENUM('en_attente','partiel','valide','rejete') NOT NULL DEFAULT 'en_attente',
    validator_id INT UNSIGNED NULL,
    validated_at DATETIME NULL,
    comment TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_val_batch_validator FOREIGN KEY (validator_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE validation_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    validation_batch_id INT UNSIGNED NOT NULL,
    user_id INT UNSIGNED NOT NULL,
    module_name VARCHAR(120) NOT NULL,
    entity_id INT UNSIGNED NOT NULL,
    decision ENUM('valide','rejete') NOT NULL,
    comment TEXT NULL,
    decided_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_validation_items_batch FOREIGN KEY (validation_batch_id) REFERENCES validation_batches(id) ON DELETE CASCADE,
    CONSTRAINT fk_validation_items_user FOREIGN KEY (user_id) REFERENCES users(id),
    UNIQUE KEY uq_validation_item (validation_batch_id, user_id, module_name, entity_id)
) ENGINE=InnoDB;

CREATE TABLE user_project_permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NOT NULL,
    project_id INT UNSIGNED NOT NULL,
    can_read TINYINT(1) NOT NULL DEFAULT 1,
    can_write TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_project_permissions_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_project_permissions_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    UNIQUE KEY uq_user_project_permissions (user_id, project_id)
) ENGINE=InnoDB;

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    module VARCHAR(100) NOT NULL,
    entity_id INT UNSIGNED NULL,
    notes TEXT NULL,
    ip_address VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_module_action (module, action)
) ENGINE=InnoDB;