-- ============================================================
--  CareBill — revenue recognition.
--  Existing database:  mysql carebill < sql/revenue.sql
--
--  One row per invoice line per month of the period it covers. Billing and
--  revenue are different things: an annual AMC invoiced in April is one entry
--  in the sales register and twelve in the revenue ledger.
-- ============================================================

CREATE TABLE revenue_schedule (
  id              BIGINT AUTO_INCREMENT PRIMARY KEY,
  invoice_id      INT NOT NULL,
  invoice_line_id BIGINT NOT NULL,
  client_id       INT NOT NULL,
  brand_id        INT NOT NULL,
  item_id         INT NULL,
  revenue_head    VARCHAR(80),
  period_month    CHAR(7) NOT NULL,              -- YYYY-MM, the month the revenue belongs to
  days            INT DEFAULT 0,                 -- days of the service period falling in that month
  amount          DECIMAL(14,2) NOT NULL,        -- in the invoice's currency, excluding GST
  base_amount     DECIMAL(16,2) NOT NULL,        -- the same at the invoice's own rate
  currency        CHAR(3) DEFAULT 'INR',
  kind            ENUM('recognised','reversal') DEFAULT 'recognised',
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_rev (invoice_line_id, period_month, kind),
  INDEX ix_rev_month (period_month),
  INDEX ix_rev_invoice (invoice_id),
  INDEX ix_rev_client (client_id, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- a month, once closed, stops moving
CREATE TABLE revenue_periods (
  period_month    CHAR(7) PRIMARY KEY,
  closed_at       DATETIME NULL,
  closed_by       INT NULL,
  note            VARCHAR(200)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (k,v) VALUES ('revenue_enabled','1'), ('revenue_basis','period')
ON DUPLICATE KEY UPDATE k=k;