SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS payment_sequences(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,prefix VARCHAR(20) DEFAULT 'PAY-',include_year TINYINT(1) DEFAULT 1,include_month TINYINT(1) DEFAULT 0,padding TINYINT UNSIGNED DEFAULT 6,next_value BIGINT UNSIGNED DEFAULT 1,current_year SMALLINT UNSIGNED,reset_annually TINYINT(1) DEFAULT 1,UNIQUE(company_id),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payment_methods(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,code VARCHAR(30) NOT NULL,name VARCHAR(100) NOT NULL,method_type VARCHAR(30) NOT NULL,requires_reference TINYINT(1) DEFAULT 0,requires_bank TINYINT(1) DEFAULT 0,requires_due_date TINYINT(1) DEFAULT 0,icon VARCHAR(80),sort_order SMALLINT DEFAULT 0,is_active TINYINT(1) DEFAULT 1,UNIQUE(company_id,code),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cashboxes(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,code VARCHAR(30) NOT NULL,name VARCHAR(100) NOT NULL,currency_code CHAR(3) DEFAULT 'MAD',manager_id BIGINT UNSIGNED,opening_balance DECIMAL(19,4) DEFAULT 0,status VARCHAR(20) DEFAULT 'active',location VARCHAR(190),created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(company_id,code),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(manager_id)REFERENCES users(id),CHECK(status IN('active','inactive','archived')))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bank_accounts(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,code VARCHAR(30) NOT NULL,name VARCHAR(120) NOT NULL,bank_name VARCHAR(120) NOT NULL,account_holder VARCHAR(190),rib VARCHAR(50),iban VARCHAR(50),swift VARCHAR(20),currency_code CHAR(3) DEFAULT 'MAD',opening_balance DECIMAL(19,4) DEFAULT 0,status VARCHAR(20) DEFAULT 'active',notes TEXT,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(company_id,code),UNIQUE(company_id,iban),FOREIGN KEY(company_id)REFERENCES companies(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cashbox_sessions(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,cashbox_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED NOT NULL,opened_at DATETIME NOT NULL,opening_amount DECIMAL(19,4) NOT NULL,closed_at DATETIME,theoretical_amount DECIMAL(19,4),counted_amount DECIMAL(19,4),difference_amount DECIMAL(19,4),justification VARCHAR(500),status VARCHAR(20) DEFAULT 'open',INDEX(cashbox_id,user_id,status),FOREIGN KEY(cashbox_id)REFERENCES cashboxes(id),FOREIGN KEY(user_id)REFERENCES users(id),CHECK(status IN('open','closed')))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS customer_payments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,number VARCHAR(50) NOT NULL,idempotency_key CHAR(36) NOT NULL,customer_id BIGINT UNSIGNED NOT NULL,payment_date DATE NOT NULL,amount DECIMAL(19,4) NOT NULL,currency_code CHAR(3) DEFAULT 'MAD',payment_method_id BIGINT UNSIGNED NOT NULL,cashbox_id BIGINT UNSIGNED,bank_account_id BIGINT UNSIGNED,cashbox_session_id BIGINT UNSIGNED,external_reference VARCHAR(120),check_number VARCHAR(80),check_bank VARCHAR(120),due_date DATE,bearer VARCHAR(190),internal_notes TEXT,receipt_comment TEXT,allocated_amount DECIMAL(19,4) DEFAULT 0,unallocated_amount DECIMAL(19,4) NOT NULL,status VARCHAR(30) DEFAULT 'draft',validated_by BIGINT UNSIGNED,validated_at DATETIME,cancelled_at DATETIME,created_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(company_id,number),UNIQUE(idempotency_key),INDEX idx_payment_filters(company_id,status,payment_date,customer_id),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(customer_id)REFERENCES third_parties(id),FOREIGN KEY(payment_method_id)REFERENCES payment_methods(id),FOREIGN KEY(cashbox_id)REFERENCES cashboxes(id),FOREIGN KEY(bank_account_id)REFERENCES bank_accounts(id),FOREIGN KEY(cashbox_session_id)REFERENCES cashbox_sessions(id),FOREIGN KEY(validated_by)REFERENCES users(id),FOREIGN KEY(created_by)REFERENCES users(id),CHECK(amount>0),CHECK(status IN('draft','validated','partially_allocated','fully_allocated','pending','rejected','cancelled','archived')))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payment_allocations(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_payment_id BIGINT UNSIGNED NOT NULL,customer_invoice_id BIGINT UNSIGNED NOT NULL,invoice_due_date_id BIGINT UNSIGNED,amount DECIMAL(19,4) NOT NULL,idempotency_key CHAR(64) NOT NULL,allocated_by BIGINT UNSIGNED NOT NULL,allocated_at DATETIME DEFAULT CURRENT_TIMESTAMP,reversed_at DATETIME,UNIQUE(idempotency_key),INDEX(customer_invoice_id,reversed_at),FOREIGN KEY(customer_payment_id)REFERENCES customer_payments(id),FOREIGN KEY(customer_invoice_id)REFERENCES customer_invoices(id),FOREIGN KEY(invoice_due_date_id)REFERENCES invoice_due_dates(id),FOREIGN KEY(allocated_by)REFERENCES users(id),CHECK(amount>0))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS customer_credits(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,customer_id BIGINT UNSIGNED NOT NULL,customer_payment_id BIGINT UNSIGNED NOT NULL,credit_amount DECIMAL(19,4) NOT NULL,used_amount DECIMAL(19,4) DEFAULT 0,status VARCHAR(20) DEFAULT 'available',created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(customer_payment_id),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(customer_id)REFERENCES third_parties(id),FOREIGN KEY(customer_payment_id)REFERENCES customer_payments(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS cash_movements(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,cashbox_id BIGINT UNSIGNED NOT NULL,cashbox_session_id BIGINT UNSIGNED,movement_type VARCHAR(30) NOT NULL,amount_delta DECIMAL(19,4) NOT NULL,movement_date DATETIME NOT NULL,reference VARCHAR(100) NOT NULL,source_type VARCHAR(40),source_id BIGINT UNSIGNED,customer_id BIGINT UNSIGNED,user_id BIGINT UNSIGNED NOT NULL,reason VARCHAR(500) NOT NULL,reversal_of_id BIGINT UNSIGNED,transfer_group CHAR(36),UNIQUE(source_type,source_id,movement_type),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(cashbox_id)REFERENCES cashboxes(id),FOREIGN KEY(cashbox_session_id)REFERENCES cashbox_sessions(id),FOREIGN KEY(customer_id)REFERENCES third_parties(id),FOREIGN KEY(user_id)REFERENCES users(id),FOREIGN KEY(reversal_of_id)REFERENCES cash_movements(id),CHECK(amount_delta<>0))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bank_movements(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,bank_account_id BIGINT UNSIGNED NOT NULL,movement_type VARCHAR(30) NOT NULL,amount_delta DECIMAL(19,4) NOT NULL,movement_date DATETIME NOT NULL,reference VARCHAR(100) NOT NULL,source_type VARCHAR(40),source_id BIGINT UNSIGNED,user_id BIGINT UNSIGNED NOT NULL,reason VARCHAR(500),reversal_of_id BIGINT UNSIGNED,transfer_group CHAR(36),reconciled_at DATETIME,UNIQUE(source_type,source_id,movement_type),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(bank_account_id)REFERENCES bank_accounts(id),FOREIGN KEY(user_id)REFERENCES users(id),FOREIGN KEY(reversal_of_id)REFERENCES bank_movements(id),CHECK(amount_delta<>0))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS internal_transfers(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,transfer_uuid CHAR(36) NOT NULL,source_type VARCHAR(20) NOT NULL,source_id BIGINT UNSIGNED NOT NULL,destination_type VARCHAR(20) NOT NULL,destination_id BIGINT UNSIGNED NOT NULL,amount DECIMAL(19,4) NOT NULL,currency_code CHAR(3) DEFAULT 'MAD',reference VARCHAR(100),user_id BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(transfer_uuid),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(user_id)REFERENCES users(id),CHECK(amount>0))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS customer_checks(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,customer_payment_id BIGINT UNSIGNED NOT NULL,check_number VARCHAR(80) NOT NULL,bank_name VARCHAR(120) NOT NULL,customer_id BIGINT UNSIGNED NOT NULL,amount DECIMAL(19,4) NOT NULL,received_date DATE NOT NULL,due_date DATE,status VARCHAR(30) DEFAULT 'received',deposit_bank_account_id BIGINT UNSIGNED,deposited_at DATETIME,cashed_at DATETIME,rejection_reason VARCHAR(500),UNIQUE(company_id,bank_name,check_number),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(customer_payment_id)REFERENCES customer_payments(id),FOREIGN KEY(customer_id)REFERENCES third_parties(id),FOREIGN KEY(deposit_bank_account_id)REFERENCES bank_accounts(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payment_status_history(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_payment_id BIGINT UNSIGNED NOT NULL,user_id BIGINT UNSIGNED,action VARCHAR(50) NOT NULL,old_status VARCHAR(30),new_status VARCHAR(30),details JSON,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(customer_payment_id)REFERENCES customer_payments(id),FOREIGN KEY(user_id)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS payment_attachments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_payment_id BIGINT UNSIGNED NOT NULL,original_name VARCHAR(255) NOT NULL,stored_name VARCHAR(255) NOT NULL,mime_type VARCHAR(120) NOT NULL,size_bytes BIGINT UNSIGNED NOT NULL,sha256 CHAR(64) NOT NULL,uploaded_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(stored_name),FOREIGN KEY(customer_payment_id)REFERENCES customer_payments(id),FOREIGN KEY(uploaded_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bank_statement_imports(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,bank_account_id BIGINT UNSIGNED NOT NULL,filename VARCHAR(255) NOT NULL,imported_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(bank_account_id)REFERENCES bank_accounts(id),FOREIGN KEY(imported_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bank_statement_lines(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,import_id BIGINT UNSIGNED NOT NULL,line_date DATE NOT NULL,description VARCHAR(500),reference VARCHAR(120),amount DECIMAL(19,4) NOT NULL,is_reconciled TINYINT(1) DEFAULT 0,UNIQUE(import_id,line_date,reference,amount),FOREIGN KEY(import_id)REFERENCES bank_statement_imports(id)ON DELETE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bank_reconciliations(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,statement_line_id BIGINT UNSIGNED NOT NULL,bank_movement_id BIGINT UNSIGNED NOT NULL,reconciled_by BIGINT UNSIGNED NOT NULL,reconciled_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(statement_line_id),UNIQUE(bank_movement_id),FOREIGN KEY(statement_line_id)REFERENCES bank_statement_lines(id),FOREIGN KEY(bank_movement_id)REFERENCES bank_movements(id),FOREIGN KEY(reconciled_by)REFERENCES users(id))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO payment_sequences(company_id,current_year)SELECT id,YEAR(CURDATE())FROM companies;
INSERT IGNORE INTO payment_methods(company_id,code,name,method_type,requires_reference,requires_bank,requires_due_date,sort_order)SELECT id,'CASH','Espèces','cash',0,0,0,10 FROM companies UNION ALL SELECT id,'TRANSFER','Virement bancaire','bank',1,1,0,20 FROM companies UNION ALL SELECT id,'CHECK','Chèque','check',1,1,1,30 FROM companies UNION ALL SELECT id,'CARD','Carte bancaire','card',1,1,0,40 FROM companies UNION ALL SELECT id,'MOBILE','Paiement mobile','mobile',1,0,0,50 FROM companies UNION ALL SELECT id,'BILL','Effet','bill',1,1,1,60 FROM companies UNION ALL SELECT id,'OFFSET','Compensation','offset',1,0,0,70 FROM companies UNION ALL SELECT id,'OTHER','Autre','other',0,0,0,80 FROM companies;
INSERT IGNORE INTO permissions(module,action,name)VALUES('payments','view','Paiements — Afficher'),('payments','create','Paiements — Créer'),('payments','update_draft','Paiements — Modifier'),('payments','validate','Paiements — Valider'),('payments','allocate','Paiements — Affecter'),('payments','cancel','Paiements — Annuler'),('payments','print','Paiements — Imprimer'),('payments','export','Paiements — Exporter'),('payments','manage_advance','Paiements — Avances'),('payments','manage_attachments','Paiements — Pièces jointes'),('payments','override_allocation','Paiements — Forcer affectation'),('cashboxes','view','Caisses — Afficher'),('cashboxes','create','Caisses — Créer'),('cashboxes','update','Caisses — Modifier'),('cashboxes','open','Caisses — Ouvrir'),('cashboxes','close','Caisses — Clôturer'),('cashboxes','add_movement','Caisses — Mouvement'),('cashboxes','transfer','Caisses — Transfert'),('cashboxes','view_balance','Caisses — Solde'),('cashboxes','export','Caisses — Exporter'),('cashboxes','correct','Caisses — Corriger'),('bank_accounts','view','Banques — Afficher'),('bank_accounts','create','Banques — Créer'),('bank_accounts','update','Banques — Modifier'),('bank_accounts','add_movement','Banques — Mouvement'),('bank_accounts','transfer','Banques — Transfert'),('bank_accounts','view_balance','Banques — Solde'),('bank_accounts','reconcile','Banques — Rapprocher'),('bank_accounts','export','Banques — Exporter'),('bank_accounts','correct','Banques — Corriger'),('checks','view','Chèques — Afficher'),('checks','create','Chèques — Créer'),('checks','deposit','Chèques — Déposer'),('checks','cash','Chèques — Encaisser'),('checks','reject','Chèques — Rejeter'),('checks','cancel','Chèques — Annuler');
INSERT IGNORE INTO role_permissions(role_id,permission_id)SELECT r.id,p.id FROM roles r CROSS JOIN permissions p WHERE r.code='administrator'AND p.module IN('payments','cashboxes','bank_accounts','checks');