SET NAMES utf8mb4;
ALTER TABLE bank_statement_imports
 ADD COLUMN IF NOT EXISTS company_id BIGINT UNSIGNED NULL AFTER id,
 ADD COLUMN IF NOT EXISTS original_name VARCHAR(255) NULL AFTER bank_account_id,
 ADD COLUMN IF NOT EXISTS stored_name VARCHAR(255) NULL AFTER original_name,
 ADD COLUMN IF NOT EXISTS file_hash CHAR(64) NULL AFTER stored_name,
 ADD COLUMN IF NOT EXISTS delimiter CHAR(1) NOT NULL DEFAULT ';' AFTER file_hash,
 ADD COLUMN IF NOT EXISTS imported_rows INT UNSIGNED NOT NULL DEFAULT 0 AFTER delimiter,
 ADD COLUMN IF NOT EXISTS matched_rows INT UNSIGNED NOT NULL DEFAULT 0 AFTER imported_rows,
 ADD COLUMN IF NOT EXISTS status VARCHAR(20) NOT NULL DEFAULT 'completed' AFTER matched_rows,
 ADD COLUMN IF NOT EXISTS completed_at DATETIME NULL AFTER created_at;
UPDATE bank_statement_imports i JOIN bank_accounts b ON b.id=i.bank_account_id
 SET i.company_id=b.company_id,i.original_name=COALESCE(i.original_name,i.filename),i.stored_name=COALESCE(i.stored_name,i.filename),
 i.file_hash=COALESCE(i.file_hash,SHA2(CONCAT('legacy-',i.id,'-',i.filename),256));
ALTER TABLE bank_statement_imports
 MODIFY company_id BIGINT UNSIGNED NOT NULL,MODIFY original_name VARCHAR(255) NOT NULL,MODIFY stored_name VARCHAR(255) NOT NULL,MODIFY file_hash CHAR(64) NOT NULL,
 ADD INDEX IF NOT EXISTS idx_statement_import_company(company_id,bank_account_id,created_at),
 ADD UNIQUE INDEX IF NOT EXISTS uq_statement_file(company_id,bank_account_id,file_hash),
 ADD CONSTRAINT fk_statement_import_company FOREIGN KEY(company_id)REFERENCES companies(id);

ALTER TABLE bank_statement_lines
 ADD COLUMN IF NOT EXISTS bank_account_id BIGINT UNSIGNED NULL AFTER import_id,
 ADD COLUMN IF NOT EXISTS row_number INT UNSIGNED NULL AFTER bank_account_id,
 ADD COLUMN IF NOT EXISTS booking_date DATE NULL AFTER row_number,
 ADD COLUMN IF NOT EXISTS value_date DATE NULL AFTER booking_date,
 ADD COLUMN IF NOT EXISTS balance DECIMAL(19,4) NULL AFTER amount,
 ADD COLUMN IF NOT EXISTS row_hash CHAR(64) NULL AFTER balance,
 ADD COLUMN IF NOT EXISTS match_status VARCHAR(20) NOT NULL DEFAULT 'unmatched' AFTER row_hash,
 ADD COLUMN IF NOT EXISTS matched_bank_movement_id BIGINT UNSIGNED NULL AFTER match_status,
 ADD COLUMN IF NOT EXISTS matched_by BIGINT UNSIGNED NULL AFTER matched_bank_movement_id,
 ADD COLUMN IF NOT EXISTS matched_at DATETIME NULL AFTER matched_by,
 ADD COLUMN IF NOT EXISTS match_note VARCHAR(500) NULL AFTER matched_at,
 ADD COLUMN IF NOT EXISTS created_at DATETIME DEFAULT CURRENT_TIMESTAMP AFTER match_note;
UPDATE bank_statement_lines l JOIN bank_statement_imports i ON i.id=l.import_id
 SET l.bank_account_id=i.bank_account_id,l.row_number=COALESCE(l.row_number,l.id),l.booking_date=COALESCE(l.booking_date,l.line_date),
 l.row_hash=COALESCE(l.row_hash,SHA2(CONCAT('legacy-',l.id,'-',l.line_date,'-',l.amount),256)),
 l.match_status=CASE WHEN l.is_reconciled=1 THEN 'matched' ELSE l.match_status END;
ALTER TABLE bank_statement_lines
 MODIFY bank_account_id BIGINT UNSIGNED NOT NULL,MODIFY row_number INT UNSIGNED NOT NULL,MODIFY booking_date DATE NOT NULL,MODIFY row_hash CHAR(64) NOT NULL,
 ADD UNIQUE INDEX IF NOT EXISTS uq_statement_row(bank_account_id,row_hash),
 ADD INDEX IF NOT EXISTS idx_statement_match(bank_account_id,match_status,booking_date,amount),
 ADD CONSTRAINT fk_statement_line_account FOREIGN KEY(bank_account_id)REFERENCES bank_accounts(id),
 ADD CONSTRAINT fk_statement_line_movement FOREIGN KEY(matched_bank_movement_id)REFERENCES bank_movements(id),
 ADD CONSTRAINT fk_statement_line_user FOREIGN KEY(matched_by)REFERENCES users(id);