SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS bank_statement_imports(
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,company_id BIGINT UNSIGNED NOT NULL,bank_account_id BIGINT UNSIGNED NOT NULL,
 original_name VARCHAR(255) NOT NULL,stored_name VARCHAR(255) NOT NULL,file_hash CHAR(64) NOT NULL,delimiter CHAR(1) NOT NULL,
 imported_rows INT UNSIGNED NOT NULL DEFAULT 0,matched_rows INT UNSIGNED NOT NULL DEFAULT 0,status VARCHAR(20) NOT NULL DEFAULT 'processing',
 imported_by BIGINT UNSIGNED NOT NULL,created_at DATETIME DEFAULT CURRENT_TIMESTAMP,completed_at DATETIME,
 UNIQUE(company_id,bank_account_id,file_hash),FOREIGN KEY(company_id)REFERENCES companies(id),FOREIGN KEY(bank_account_id)REFERENCES bank_accounts(id),FOREIGN KEY(imported_by)REFERENCES users(id),
 CHECK(status IN('processing','completed','failed'))
)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,bank_account_id BIGINT UNSIGNED NOT NULL,row_number INT UNSIGNED NOT NULL,
 booking_date DATE NOT NULL,value_date DATE,description VARCHAR(500) NOT NULL,reference VARCHAR(150),amount DECIMAL(19,4) NOT NULL,balance DECIMAL(19,4),row_hash CHAR(64) NOT NULL,
 match_status VARCHAR(20) NOT NULL DEFAULT 'unmatched',matched_bank_movement_id BIGINT UNSIGNED,matched_by BIGINT UNSIGNED,matched_at DATETIME,match_note VARCHAR(500),
 created_at DATETIME DEFAULT CURRENT_TIMESTAMP,UNIQUE(bank_account_id,row_hash),INDEX idx_statement_match(bank_account_id,match_status,booking_date,amount),
 FOREIGN KEY(import_id)REFERENCES bank_statement_imports(id)ON DELETE CASCADE,FOREIGN KEY(bank_account_id)REFERENCES bank_accounts(id),FOREIGN KEY(matched_bank_movement_id)REFERENCES bank_movements(id),FOREIGN KEY(matched_by)REFERENCES users(id),
 CHECK(match_status IN('unmatched','matched','ignored'))
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO permissions(module,action,name)VALUES
('bank_reconciliation','view','Rapprochement bancaire — Consulter'),('bank_reconciliation','import','Rapprochement bancaire — Importer'),
('bank_reconciliation','match','Rapprochement bancaire — Rapprocher'),('bank_reconciliation','ignore','Rapprochement bancaire — Ignorer');
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='bank_reconciliation';