-- Module comptabilité suisse: plan comptable, écritures, CAMT, banque, achats et TVA.
-- Le service Node crée aussi ces tables automatiquement au premier accès.

CREATE TABLE IF NOT EXISTS accounting_accounts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  code VARCHAR(20) NOT NULL,
  name VARCHAR(190) NOT NULL,
  type ENUM('asset','liability','equity','revenue','expense') NOT NULL,
  category VARCHAR(120),
  vat_code VARCHAR(40),
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_accounting_accounts_company_code (company_id, code),
  INDEX idx_accounting_accounts_company_type (company_id, type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounting_settings (
  company_id INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  value TEXT,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (company_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounting_periods (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  label VARCHAR(120) NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  status ENUM('open','closed','locked') NOT NULL DEFAULT 'open',
  closed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_accounting_periods_company_range (company_id, starts_on, ends_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounting_entries (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  journal_code VARCHAR(40) NOT NULL DEFAULT 'general',
  entry_date DATE NOT NULL,
  value_date DATE NULL,
  document_type VARCHAR(60),
  document_id INT UNSIGNED NULL,
  source VARCHAR(80),
  source_id INT UNSIGNED NULL,
  label VARCHAR(255) NOT NULL,
  reference VARCHAR(120),
  currency CHAR(3) NOT NULL DEFAULT 'CHF',
  status ENUM('draft','posted','locked') NOT NULL DEFAULT 'posted',
  total_debit DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_credit DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_accounting_entry_source (company_id, source, source_id),
  INDEX idx_accounting_entries_company_date (company_id, entry_date),
  INDEX idx_accounting_entries_document (company_id, document_type, document_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounting_entry_lines (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  entry_id INT UNSIGNED NOT NULL,
  account_id INT UNSIGNED NOT NULL,
  description VARCHAR(255),
  debit DECIMAL(12,2) NOT NULL DEFAULT 0,
  credit DECIMAL(12,2) NOT NULL DEFAULT 0,
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
  vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  position INT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_accounting_lines_company_account (company_id, account_id),
  CONSTRAINT fk_accounting_lines_entry FOREIGN KEY (entry_id) REFERENCES accounting_entries(id) ON DELETE CASCADE,
  CONSTRAINT fk_accounting_lines_account FOREIGN KEY (account_id) REFERENCES accounting_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bank_statement_imports (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  bank_account_id INT UNSIGNED NULL,
  message_type VARCHAR(40) NOT NULL DEFAULT 'camt',
  message_id VARCHAR(190),
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255),
  imported_by INT UNSIGNED NULL,
  total_transactions INT UNSIGNED NOT NULL DEFAULT 0,
  matched_transactions INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('imported','partial','matched','error') NOT NULL DEFAULT 'imported',
  notes TEXT,
  imported_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_bank_imports_company (company_id, imported_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bank_transactions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  bank_account_id INT UNSIGNED NULL,
  import_id INT UNSIGNED NULL,
  message_type VARCHAR(40),
  statement_id VARCHAR(190),
  entry_reference VARCHAR(190),
  transaction_reference VARCHAR(190),
  amount DECIMAL(12,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'CHF',
  direction ENUM('credit','debit') NOT NULL,
  booking_date DATE NULL,
  value_date DATE NULL,
  counterparty_name VARCHAR(190),
  counterparty_iban VARCHAR(34),
  payment_reference VARCHAR(190),
  remittance_info TEXT,
  status ENUM('unmatched','matched','ignored','booked') NOT NULL DEFAULT 'unmatched',
  invoice_id INT UNSIGNED NULL,
  payment_id INT UNSIGNED NULL,
  payroll_run_id INT UNSIGNED NULL,
  payroll_payslip_id INT UNSIGNED NULL,
  accounting_entry_id INT UNSIGNED NULL,
  match_score INT UNSIGNED NOT NULL DEFAULT 0,
  raw_json JSON,
  hash CHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_bank_transaction_hash (company_id, hash),
  INDEX idx_bank_transactions_company_status (company_id, status),
  INDEX idx_bank_transactions_company_date (company_id, booking_date),
  CONSTRAINT fk_bank_transactions_import FOREIGN KEY (import_id) REFERENCES bank_statement_imports(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS accounting_expenses (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_id INT UNSIGNED NOT NULL,
  supplier_name VARCHAR(190) NOT NULL,
  supplier_invoice_number VARCHAR(120),
  expense_date DATE NOT NULL,
  due_date DATE NULL,
  account_id INT UNSIGNED NOT NULL,
  subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
  vat_rate DECIMAL(5,2) NOT NULL DEFAULT 0,
  vat_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  total DECIMAL(12,2) NOT NULL DEFAULT 0,
  paid_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  status ENUM('open','paid','cancelled') NOT NULL DEFAULT 'open',
  paid_at DATE NULL,
  attachment_path VARCHAR(255),
  notes TEXT,
  accounting_entry_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_accounting_expenses_company_status (company_id, status),
  INDEX idx_accounting_expenses_company_date (company_id, expense_date),
  CONSTRAINT fk_accounting_expenses_account FOREIGN KEY (account_id) REFERENCES accounting_accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;