SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS companies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    legal_name VARCHAR(190) NOT NULL,
    currency_code CHAR(3) NOT NULL DEFAULT 'MAD',
    timezone VARCHAR(64) NOT NULL DEFAULT 'Africa/Casablanca',
    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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    username VARCHAR(80) NOT NULL,
    email VARCHAR(190) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(40) NULL,
    photo_path VARCHAR(255) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    failed_login_count SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until DATETIME NULL,
    last_login_at DATETIME NULL,
    password_changed_at DATETIME NULL,
    must_change_password 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,
    CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT,
    CONSTRAINT uq_users_username UNIQUE (username),
    CONSTRAINT uq_users_email UNIQUE (email),
    CONSTRAINT chk_users_status CHECK (status IN ('active','inactive')),
    INDEX idx_users_company_status (company_id, status),
    INDEX idx_users_name (company_id, last_name, first_name),
    INDEX idx_users_locked_until (locked_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(80) NOT NULL,
    description VARCHAR(255) NULL,
    is_system TINYINT(1) NOT NULL DEFAULT 0,
    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,
    CONSTRAINT fk_roles_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT,
    CONSTRAINT uq_roles_company_code UNIQUE (company_id, code),
    INDEX idx_roles_company_active (company_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module VARCHAR(80) NOT NULL,
    action VARCHAR(80) NOT NULL,
    name VARCHAR(160) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_permissions_module_action UNIQUE (module, action),
    INDEX idx_permissions_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT 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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_roles (
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_user_roles_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_user_roles_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT,
    INDEX idx_user_roles_role (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(190) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    was_successful TINYINT(1) NOT NULL DEFAULT 0,
    attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_login_attempts_identifier_time (identifier, attempted_at),
    INDEX idx_login_attempts_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS remember_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    selector CHAR(24) NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_remember_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT uq_remember_tokens_selector UNIQUE (selector),
    INDEX idx_remember_tokens_user_expiry (user_id, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(80) NOT NULL,
    module VARCHAR(80) NOT NULL,
    description VARCHAR(500) NOT NULL,
    entity_type VARCHAR(80) NULL,
    entity_id BIGINT UNSIGNED NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(500) NULL,
    metadata JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_activity_logs_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE RESTRICT,
    CONSTRAINT fk_activity_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_activity_logs_company_date (company_id, created_at),
    INDEX idx_activity_logs_user_date (user_id, created_at),
    INDEX idx_activity_logs_module_action (module, action),
    INDEX idx_activity_logs_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

INSERT IGNORE INTO companies (id, legal_name, currency_code, timezone) VALUES
(1, 'Ma Société', 'MAD', 'Africa/Casablanca');

INSERT IGNORE INTO roles (id, company_id, name, code, description, is_system, is_active) VALUES
(1, 1, 'Administrateur', 'administrator', 'Accès complet et non révocable.', 1, 1),
(2, 1, 'Commercial', 'sales', 'Gestion des activités commerciales.', 1, 1),
(3, 1, 'Caissier', 'cashier', 'Gestion des encaissements et de la caisse.', 1, 1),
(4, 1, 'Responsable stock', 'stock_manager', 'Gestion du stock et des entrepôts.', 1, 1),
(5, 1, 'Comptable', 'accountant', 'Facturation, banque, dépenses et rapports.', 1, 1),
(6, 1, 'Livreur', 'delivery_driver', 'Consultation et traitement des livraisons.', 1, 1);

INSERT IGNORE INTO users (id, company_id, username, email, password_hash, first_name, last_name, status, must_change_password) VALUES
(1, 1, 'admin', 'admin@example.com', '$2y$10$yfTprsOtIrM3ykmio0HmNuRUwRlvTTllF8D.wesmmqxkYD6yORDYi', 'Administrateur', 'Système', 'active', 1);
INSERT IGNORE INTO user_roles (user_id, role_id) VALUES (1, 1);

INSERT IGNORE INTO permissions (module, action, name)
SELECT modules.module, actions.action, CONCAT(modules.label, ' — ', actions.label)
FROM (
    SELECT 'dashboard' module, 'Tableau de bord' label UNION ALL SELECT 'customers','Clients' UNION ALL
    SELECT 'suppliers','Fournisseurs' UNION ALL SELECT 'products','Produits' UNION ALL
    SELECT 'categories','Catégories' UNION ALL SELECT 'quotes','Devis' UNION ALL
    SELECT 'sales_orders','Commandes clients' UNION ALL SELECT 'delivery_notes','Bons de livraison' UNION ALL
    SELECT 'sales_invoices','Factures clients' UNION ALL SELECT 'payments','Paiements' UNION ALL
    SELECT 'purchases','Achats fournisseurs' UNION ALL SELECT 'stock','Stock' UNION ALL
    SELECT 'warehouses','Entrepôts' UNION ALL SELECT 'expenses','Dépenses' UNION ALL
    SELECT 'cash_registers','Caisses' UNION ALL SELECT 'banks','Banques' UNION ALL
    SELECT 'reports','Rapports' UNION ALL SELECT 'settings','Paramètres' UNION ALL
    SELECT 'users','Utilisateurs' UNION ALL SELECT 'roles','Rôles' UNION ALL
    SELECT 'activity_logs','Journal d’activité'
) modules
CROSS JOIN (
    SELECT 'view' action, 'Afficher' label UNION ALL SELECT 'create','Ajouter' UNION ALL
    SELECT 'edit','Modifier' UNION ALL SELECT 'delete','Supprimer' UNION ALL
    SELECT 'validate','Valider' UNION ALL SELECT 'cancel','Annuler' UNION ALL
    SELECT 'print','Imprimer' UNION ALL SELECT 'export','Exporter' UNION ALL
    SELECT 'view_purchase_prices','Voir les prix d’achat' UNION ALL SELECT 'view_profits','Voir les bénéfices'
) actions;

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions;

-- Permissions initiales prudentes des rôles non administrateurs.
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.code = 'sales' AND p.module IN ('dashboard','customers','quotes','sales_orders','delivery_notes','sales_invoices') AND p.action IN ('view','create','edit','print');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.code = 'cashier' AND p.module IN ('dashboard','payments','cash_registers') AND p.action IN ('view','create','edit','validate','print');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.code = 'stock_manager' AND p.module IN ('dashboard','products','categories','stock','warehouses') AND p.action IN ('view','create','edit','validate','print','export','view_purchase_prices');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.code = 'accountant' AND p.module IN ('dashboard','sales_invoices','payments','purchases','expenses','cash_registers','banks','reports') AND p.action IN ('view','create','edit','validate','cancel','print','export','view_purchase_prices','view_profits');
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p
WHERE r.code = 'delivery_driver' AND p.module IN ('dashboard','delivery_notes') AND p.action IN ('view','validate','print');