USE comptaai_maroc;

ALTER TABLE documents DROP FOREIGN KEY documents_client_fk;

ALTER TABLE documents
  MODIFY client_id BIGINT UNSIGNED NOT NULL,
  MODIFY document_type ENUM('invoice','invoice_purchase','invoice_sale','receipt','bank_statement','tax_document','other') NOT NULL DEFAULT 'invoice_purchase',
  MODIFY status ENUM('uploaded','waiting_ocr','ocr_processed','needs_review','validated','exported','rejected','error') NOT NULL DEFAULT 'uploaded';

UPDATE documents SET document_type = 'invoice_purchase' WHERE document_type = 'invoice';
UPDATE documents SET status = 'rejected' WHERE status = 'error';

ALTER TABLE documents
  MODIFY document_type ENUM('invoice_purchase','invoice_sale','receipt','bank_statement','tax_document','other') NOT NULL DEFAULT 'invoice_purchase',
  CHANGE fiscal_month accounting_month TINYINT UNSIGNED NOT NULL,
  CHANGE fiscal_year accounting_year SMALLINT UNSIGNED NOT NULL,
  ADD COLUMN notes TEXT NULL AFTER accounting_year,
  ADD COLUMN stored_name VARCHAR(255) NULL AFTER original_name,
  MODIFY status ENUM('uploaded','waiting_ocr','ocr_processed','needs_review','validated','exported','rejected') NOT NULL DEFAULT 'uploaded',
  DROP COLUMN error_message,
  ADD INDEX documents_type_index (document_type),
  ADD INDEX documents_period_index (accounting_year, accounting_month),
  ADD INDEX documents_uploaded_by_index (uploaded_by);

UPDATE documents SET stored_name = SUBSTRING_INDEX(stored_path, '/', -1) WHERE stored_name IS NULL OR stored_name = '';

ALTER TABLE documents MODIFY stored_name VARCHAR(255) NOT NULL;

ALTER TABLE documents
  ADD CONSTRAINT documents_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE;

CREATE TABLE document_status_history (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  document_id BIGINT UNSIGNED NOT NULL,
  old_status ENUM('uploaded','waiting_ocr','ocr_processed','needs_review','validated','exported','rejected') NULL,
  new_status ENUM('uploaded','waiting_ocr','ocr_processed','needs_review','validated','exported','rejected') NOT NULL,
  changed_by BIGINT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY document_status_history_document_index (document_id),
  CONSTRAINT document_status_history_document_fk FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
  CONSTRAINT document_status_history_changed_by_fk FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;