-- ===========================================================================
-- Migration: mini accounting package (double-entry ledger)
-- Date: 2026-07-27
--
-- schema.sql already carries these for FRESH installs. Run this ONCE against
-- an EXISTING database (phpMyAdmin -> SQL tab, paste, Go). Every statement is
-- guarded / idempotent, so re-running it is harmless.
--
-- This creates the ledger tables only. The default chart of accounts is seeded
-- per-farm on first use by the application (App\Core\Ledger::seedChart), the
-- same moment a farm first touches any accounting screen or posts its first
-- transaction — nothing to seed here by hand.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS chart_of_accounts (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  code          VARCHAR(20)   NOT NULL,
  name          VARCHAR(120)  NOT NULL,
  type          ENUM('asset','liability','equity','income','expense') NOT NULL,
  role          VARCHAR(48)   NULL,
  map_key       VARCHAR(80)   NULL,
  is_system     TINYINT(1)    NOT NULL DEFAULT 0,
  sort_order    INT UNSIGNED  NOT NULL DEFAULT 0,
  active        TINYINT(1)    NOT NULL DEFAULT 1,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_coa_code (farm_id, code),
  KEY idx_coa_farm (farm_id),
  KEY idx_coa_role (farm_id, role),
  KEY idx_coa_mapkey (farm_id, map_key),
  CONSTRAINT fk_coa_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS journal_entries (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  entry_no      VARCHAR(40)   NOT NULL,
  entry_date    DATE          NOT NULL,
  memo          VARCHAR(255)  NULL,
  source_type   VARCHAR(30)   NULL,
  source_id     INT UNSIGNED  NULL,
  is_system     TINYINT(1)    NOT NULL DEFAULT 0,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_je_source (farm_id, source_type, source_id),
  KEY idx_je_farm (farm_id, entry_date),
  CONSTRAINT fk_je_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS journal_entry_lines (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  entry_id      INT UNSIGNED  NOT NULL,
  account_id    INT UNSIGNED  NOT NULL,
  debit         DECIMAL(14,2) NOT NULL DEFAULT 0,
  credit        DECIMAL(14,2) NOT NULL DEFAULT 0,
  memo          VARCHAR(255)  NULL,
  sort_order    INT UNSIGNED  NOT NULL DEFAULT 0,
  KEY idx_jel_entry (entry_id),
  KEY idx_jel_account (account_id),
  CONSTRAINT fk_jel_entry FOREIGN KEY (entry_id) REFERENCES journal_entries(id) ON DELETE CASCADE,
  CONSTRAINT fk_jel_account FOREIGN KEY (account_id) REFERENCES chart_of_accounts(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sale_payments (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  sale_id       INT UNSIGNED  NOT NULL,
  amount        DECIMAL(14,2) NOT NULL DEFAULT 0,
  method        VARCHAR(40)   NOT NULL DEFAULT 'Cash',
  paid_on       DATE          NOT NULL,
  note          VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_sp_farm (farm_id),
  KEY idx_sp_sale (sale_id),
  CONSTRAINT fk_sp_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_sp_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS expense_payments (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  farm_id       INT UNSIGNED  NOT NULL,
  expense_id    INT UNSIGNED  NOT NULL,
  amount        DECIMAL(14,2) NOT NULL DEFAULT 0,
  method        VARCHAR(40)   NOT NULL DEFAULT 'Cash',
  paid_on       DATE          NOT NULL,
  note          VARCHAR(255)  NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_ep_farm (farm_id),
  KEY idx_ep_expense (expense_id),
  CONSTRAINT fk_ep_farm FOREIGN KEY (farm_id) REFERENCES farms(id) ON DELETE CASCADE,
  CONSTRAINT fk_ep_expense FOREIGN KEY (expense_id) REFERENCES expenses(id) ON DELETE CASCADE
) ENGINE=InnoDB;