USE comptaai_maroc;

CREATE TABLE IF NOT EXISTS vat_reports (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  client_id BIGINT UNSIGNED NOT NULL,
  month TINYINT UNSIGNED NOT NULL,
  year SMALLINT UNSIGNED NOT NULL,
  collected_vat DECIMAL(14,2) NOT NULL DEFAULT 0,
  deductible_vat DECIMAL(14,2) NOT NULL DEFAULT 0,
  payable_vat DECIMAL(14,2) NOT NULL DEFAULT 0,
  status ENUM('draft','validated') 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 vat_reports_client_period_unique (client_id, year, month),
  KEY vat_reports_status_index (status),
  CONSTRAINT vat_reports_client_fk FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
  CONSTRAINT vat_reports_created_by_fk FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;