-- ---------------------------------------------------------------------------
-- 001 - subscription cost
--
-- Only needed if you imported schema.sql BEFORE the ticket cost was added.
-- A fresh import of schema.sql already includes everything here, and running
-- this against a fresh database will simply fail on the first duplicate
-- column, which is harmless.
--
-- Adds:
--   syndicates.ticket_cost   what one draw's ticket costs
--   draws.cost               the cost snapshotted onto each draw
--   draw_members.cost_share  each member's share of that cost
--   ledger entry_type 'subscription' and source_type 'draw_cost'
--
-- Run it against a backup first.
--   mysql -u USER -p DBNAME < migrations/001-subscription-cost.sql
-- ---------------------------------------------------------------------------

ALTER TABLE syndicates
  ADD COLUMN ticket_cost DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER ticket_updated_at;

ALTER TABLE draws
  ADD COLUMN cost DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER member_count;

ALTER TABLE draw_members
  ADD COLUMN cost_share DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER share_amount;

ALTER TABLE ledger
  MODIFY COLUMN entry_type
    ENUM('payment','winnings_share','subscription','opening','adjustment','reversal') NOT NULL;

ALTER TABLE ledger
  MODIFY COLUMN source_type
    ENUM('payment','draw','draw_cost','manual') NOT NULL DEFAULT 'manual';

INSERT INTO app_meta (meta_key, meta_value) VALUES ('schema_version', '2')
  ON DUPLICATE KEY UPDATE meta_value = VALUES(meta_value);