USE comptaai_maroc;

CREATE TABLE IF NOT EXISTS accounting_entries (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  document_id BIGINT UNSIGNED NOT NULL,
  entry_date DATE NOT NULL,
  journal_code VARCHAR(20) NOT NULL,
  entry_label VARCHAR(255) NOT NULL,
  status ENUM('draft','reviewed','validated','exported') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY accounting_entries_document_unique (document_id),
  KEY accounting_entries_client_index (client_id),
  KEY accounting_entries_status_index (status),
  KEY accounting_entries_period_index (entry_date),
  CONSTRAINT accounting_entries_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT accounting_entries_document_fk FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE CASCADE,
  CONSTRAINT accounting_entries_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS accounting_entry_lines (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  entry_id BIGINT UNSIGNED NOT NULL,
  account_number VARCHAR(20) NOT NULL,
  account_label VARCHAR(180) NOT NULL,
  debit DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit DECIMAL(14,2) NOT NULL DEFAULT 0,
  line_order SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  KEY accounting_entry_lines_entry_index (entry_id),
  CONSTRAINT accounting_entry_lines_entry_fk FOREIGN KEY (entry_id) REFERENCES accounting_entries(id) ON DELETE CASCADE
) ENGINE=InnoDB;