USE comptaai_maroc;

CREATE TABLE IF NOT EXISTS bank_statements (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  uploaded_by BIGINT UNSIGNED NULL,
  bank_name VARCHAR(160) NOT NULL,
  account_number VARCHAR(80) NOT NULL,
  statement_month TINYINT UNSIGNED NOT NULL,
  statement_year SMALLINT UNSIGNED NOT NULL,
  notes TEXT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  stored_path VARCHAR(255) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL,
  status ENUM('uploaded','imported') NOT NULL DEFAULT 'uploaded',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY bank_statements_client_index (client_id),
  KEY bank_statements_period_index (statement_year, statement_month),
  KEY bank_statements_status_index (status),
  CONSTRAINT bank_statements_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT bank_statements_uploaded_by_fk FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bank_transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  statement_id BIGINT UNSIGNED NOT NULL,
  client_id BIGINT UNSIGNED NOT NULL,
  transaction_date DATE NOT NULL,
  description VARCHAR(255) NOT NULL,
  debit_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  balance DECIMAL(14,2) NULL,
  category ENUM('rent','supplier_payment','client_payment','bank_fees','salary','tax','other') NOT NULL DEFAULT 'other',
  matched_document_id BIGINT UNSIGNED NULL,
  status ENUM('imported','categorized','matched','ignored') NOT NULL DEFAULT 'imported',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY bank_transactions_statement_index (statement_id),
  KEY bank_transactions_client_index (client_id),
  KEY bank_transactions_status_index (status),
  KEY bank_transactions_date_index (transaction_date),
  CONSTRAINT bank_transactions_statement_fk FOREIGN KEY (statement_id) REFERENCES bank_statements(id) ON DELETE CASCADE,
  CONSTRAINT bank_transactions_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT bank_transactions_matched_document_fk FOREIGN KEY (matched_document_id) REFERENCES documents(id) ON DELETE SET NULL
) ENGINE=InnoDB;

ALTER TABLE bank_transactions
  ADD COLUMN IF NOT EXISTS statement_id BIGINT UNSIGNED NULL AFTER id,
  ADD COLUMN IF NOT EXISTS debit_amount DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER description,
  ADD COLUMN IF NOT EXISTS credit_amount DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER debit_amount,
  ADD COLUMN IF NOT EXISTS balance DECIMAL(14,2) NULL AFTER credit_amount,
  ADD COLUMN IF NOT EXISTS category ENUM('rent','supplier_payment','client_payment','bank_fees','salary','tax','other') NOT NULL DEFAULT 'other' AFTER balance,
  ADD COLUMN IF NOT EXISTS matched_document_id BIGINT UNSIGNED NULL AFTER category;

ALTER TABLE bank_transactions
  MODIFY status ENUM('imported','categorized','matched','ignored','reconciled') NOT NULL DEFAULT 'imported';

UPDATE bank_transactions
SET debit_amount = CASE WHEN direction = 'debit' THEN amount ELSE debit_amount END,
    credit_amount = CASE WHEN direction = 'credit' THEN amount ELSE credit_amount END,
    matched_document_id = COALESCE(matched_document_id, reconciled_document_id)
WHERE (debit_amount = 0 AND credit_amount = 0)
  AND amount IS NOT NULL;