-- Phase 5 — Inventory, Email, Recurring Invoices
-- Run AFTER 004_multiuser.sql

SET FOREIGN_KEY_CHECKS = 0;

-- ─── Products ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS products (
  id                   INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id            INT UNSIGNED  NOT NULL,
  name                 VARCHAR(255)  NOT NULL,
  sku                  VARCHAR(100)  DEFAULT NULL,
  description          TEXT          DEFAULT NULL,
  unit_price           DECIMAL(15,2) DEFAULT 0.00,
  unit_cost            DECIMAL(15,2) DEFAULT 0.00,
  quantity_on_hand     DECIMAL(15,4) DEFAULT 0.0000,
  reorder_point        DECIMAL(15,4) DEFAULT 0.0000,
  inventory_account_id INT UNSIGNED  DEFAULT NULL,
  cogs_account_id      INT UNSIGNED  DEFAULT NULL,
  revenue_account_id   INT UNSIGNED  DEFAULT NULL,
  is_deleted           TINYINT(1)    DEFAULT 0,
  created_at           DATETIME      DEFAULT NOW(),
  updated_at           DATETIME      ON UPDATE NOW(),
  INDEX idx_tenant (tenant_id),
  INDEX idx_sku    (tenant_id, sku),
  CONSTRAINT fk_prod_tenant  FOREIGN KEY (tenant_id)            REFERENCES tenants(id)   ON DELETE CASCADE,
  CONSTRAINT fk_prod_inv_acct FOREIGN KEY (inventory_account_id) REFERENCES accounts(id),
  CONSTRAINT fk_prod_cogs_acct FOREIGN KEY (cogs_account_id)    REFERENCES accounts(id),
  CONSTRAINT fk_prod_rev_acct  FOREIGN KEY (revenue_account_id) REFERENCES accounts(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Inventory Movements ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS inventory_movements (
  id               INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id        INT UNSIGNED  NOT NULL,
  product_id       INT UNSIGNED  NOT NULL,
  date             DATE          NOT NULL,
  quantity         DECIMAL(15,4) NOT NULL,              -- positive = stock in, negative = stock out
  unit_cost        DECIMAL(15,2) DEFAULT 0.00,
  reference_type   ENUM('sale','purchase','adjustment') NOT NULL,
  reference_id     INT UNSIGNED  DEFAULT NULL,
  notes            TEXT          DEFAULT NULL,
  journal_entry_id INT UNSIGNED  DEFAULT NULL,
  created_at       DATETIME      DEFAULT NOW(),
  INDEX idx_tenant  (tenant_id),
  INDEX idx_product (product_id),
  CONSTRAINT fk_im_tenant  FOREIGN KEY (tenant_id)  REFERENCES tenants(id)   ON DELETE CASCADE,
  CONSTRAINT fk_im_product FOREIGN KEY (product_id) REFERENCES products(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Invoice line: link to product ───────────────────────────────────────────
ALTER TABLE invoice_lines
  ADD COLUMN IF NOT EXISTS product_id INT UNSIGNED DEFAULT NULL AFTER invoice_id;

-- ─── Invoice: track email send time ──────────────────────────────────────────
ALTER TABLE invoices
  ADD COLUMN IF NOT EXISTS emailed_at DATETIME DEFAULT NULL;

-- ─── Recurring Invoices ───────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS recurring_invoices (
  id                 INT UNSIGNED  AUTO_INCREMENT PRIMARY KEY,
  tenant_id          INT UNSIGNED  NOT NULL,
  customer_id        INT UNSIGNED  NOT NULL,
  revenue_account_id INT UNSIGNED  NOT NULL,
  created_by_id      INT UNSIGNED  NOT NULL,
  title              VARCHAR(255)  NOT NULL,
  frequency          ENUM('weekly','monthly','quarterly','annually') NOT NULL,
  next_date          DATE          NOT NULL,
  end_date           DATE          DEFAULT NULL,
  tax                DECIMAL(15,2) DEFAULT 0.00,
  notes              TEXT          DEFAULT NULL,
  `lines`            JSON          NOT NULL,
  is_active          TINYINT(1)    DEFAULT 1,
  last_run_at        DATETIME      DEFAULT NULL,
  created_at         DATETIME      DEFAULT NOW(),
  INDEX idx_tenant    (tenant_id),
  INDEX idx_next_date (tenant_id, next_date),
  CONSTRAINT fk_ri_tenant   FOREIGN KEY (tenant_id)          REFERENCES tenants(id)   ON DELETE CASCADE,
  CONSTRAINT fk_ri_customer FOREIGN KEY (customer_id)        REFERENCES customers(id),
  CONSTRAINT fk_ri_rev_acct FOREIGN KEY (revenue_account_id) REFERENCES accounts(id),
  CONSTRAINT fk_ri_user     FOREIGN KEY (created_by_id)      REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
