-- ---------------------------------------------------------------------------
-- Lotto Syndicate Manager - database schema
-- MySQL 5.7+ / MariaDB 10.2+
--
-- Import via phpMyAdmin, or:
--   mysql -u USER -p DBNAME < schema.sql
-- ---------------------------------------------------------------------------

SET NAMES utf8mb4;

-- --------------------------------------------------------------------------
-- Admin logins
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id             INT UNSIGNED NOT NULL AUTO_INCREMENT,
  username       VARCHAR(64)  NOT NULL,
  password_hash  VARCHAR(255) NOT NULL,
  full_name      VARCHAR(120) NOT NULL DEFAULT '',
  email          VARCHAR(190) NOT NULL DEFAULT '',
  is_active      TINYINT(1)   NOT NULL DEFAULT 1,
  last_login_at  DATETIME     NULL,
  created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Failed login throttling
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS login_attempts (
  id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ip           VARCHAR(45)  NOT NULL,
  username     VARCHAR(64)  NOT NULL,
  attempted_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_login_attempts_ip (ip, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Syndicates
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS syndicates (
  id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name                VARCHAR(120) NOT NULL,
  -- Which draws this syndicate buys tickets for
  draws_wed           TINYINT(1)   NOT NULL DEFAULT 1,
  draws_sat           TINYINT(1)   NOT NULL DEFAULT 1,
  -- Where a dollar win is credited: 'contributions' or 'winnings'
  winnings_allocation ENUM('contributions','winnings') NOT NULL DEFAULT 'winnings',
  -- The standing subscription ticket image: entered once, updated only
  -- when the subscription itself changes.
  ticket_image        VARCHAR(255) NULL,
  ticket_image_note   VARCHAR(255) NOT NULL DEFAULT '',
  ticket_updated_at   DATETIME     NULL,
  -- What the subscription ticket costs for one draw. Split evenly across the
  -- members on each regular draw and taken off their contributions balance.
  ticket_cost         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  from_name           VARCHAR(120) NOT NULL DEFAULT '',
  is_active           TINYINT(1)   NOT NULL DEFAULT 1,
  notes               TEXT         NULL,
  created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Members
--   Balances are running caches; `ledger` is the source of truth.
--   Members never log in. Everything they see arrives by email.
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS members (
  id                    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  syndicate_id          INT UNSIGNED  NOT NULL,
  full_name             VARCHAR(160)  NOT NULL,
  email                 VARCHAR(190)  NOT NULL,
  phone                 VARCHAR(40)   NOT NULL DEFAULT '',
  status                ENUM('active','inactive') NOT NULL DEFAULT 'active',
  contributions_balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  winnings_balance      DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  joined_on             DATE          NULL,
  notes                 TEXT          NULL,
  created_at            DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at            DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_members_syndicate (syndicate_id, status),
  KEY ix_members_email (email),
  CONSTRAINT fk_members_syndicate FOREIGN KEY (syndicate_id)
    REFERENCES syndicates (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Draws
--   draw_type 'regular' = the twice-weekly subscription entry
--   draw_type 'bonus'   = generated from bonus lines won in an earlier draw
--   dedupe_key keeps auto-generation idempotent (cron + lazy catch-up
--   can both run without ever creating a duplicate).
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS draws (
  id                  INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  syndicate_id        INT UNSIGNED  NOT NULL,
  draw_date           DATE          NOT NULL,
  draw_day            ENUM('WED','SAT') NOT NULL,
  draw_type           ENUM('regular','bonus') NOT NULL DEFAULT 'regular',
  parent_draw_id      INT UNSIGNED  NULL,
  dedupe_key          VARCHAR(80)   NOT NULL,
  ticket_image        VARCHAR(255)  NULL,
  member_count        INT UNSIGNED  NOT NULL DEFAULT 0,
  -- Ticket cost for this draw, copied from the syndicate when the draw was
  -- created so a later price change never rewrites history.
  -- Always 0.00 on a bonus draw: those tickets were won, not bought.
  cost                DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status              ENUM('pending','entered') NOT NULL DEFAULT 'pending',

  -- Results
  no_win              TINYINT(1)    NOT NULL DEFAULT 0,
  amount_won          DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  bonus_lotto_lines   INT UNSIGNED  NOT NULL DEFAULT 0,
  bonus_strike_lines  INT UNSIGNED  NOT NULL DEFAULT 0,
  bonus_ticket_image  VARCHAR(255)  NULL,
  allocated_to        ENUM('contributions','winnings') NULL,
  results_notes       TEXT          NULL,
  results_entered_at  DATETIME      NULL,

  entry_email_sent_at   DATETIME    NULL,
  results_email_sent_at DATETIME    NULL,
  created_at          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_draws_dedupe (dedupe_key),
  KEY ix_draws_syndicate_date (syndicate_id, draw_date),
  KEY ix_draws_status (status, draw_date),
  KEY ix_draws_parent (parent_draw_id),
  CONSTRAINT fk_draws_syndicate FOREIGN KEY (syndicate_id)
    REFERENCES syndicates (id) ON DELETE CASCADE,
  CONSTRAINT fk_draws_parent FOREIGN KEY (parent_draw_id)
    REFERENCES draws (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Members snapshotted into a draw at the moment it was created.
-- Name and email are copied so historical draws stay accurate even if a
-- member is later renamed or removed.
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS draw_members (
  id           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  draw_id      INT UNSIGNED  NOT NULL,
  member_id    INT UNSIGNED  NULL,
  full_name    VARCHAR(160)  NOT NULL,
  email        VARCHAR(190)  NOT NULL,
  share_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  -- This member's share of the ticket cost, charged when the draw was created.
  cost_share   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (id),
  UNIQUE KEY uq_draw_member (draw_id, member_id),
  KEY ix_draw_members_member (member_id),
  CONSTRAINT fk_draw_members_draw FOREIGN KEY (draw_id)
    REFERENCES draws (id) ON DELETE CASCADE,
  CONSTRAINT fk_draw_members_member FOREIGN KEY (member_id)
    REFERENCES members (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Contribution payments received from members
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS payments (
  id           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  member_id    INT UNSIGNED  NOT NULL,
  syndicate_id INT UNSIGNED  NOT NULL,
  amount       DECIMAL(12,2) NOT NULL,
  paid_on      DATE          NOT NULL,
  method       VARCHAR(60)   NOT NULL DEFAULT '',
  reference    VARCHAR(120)  NOT NULL DEFAULT '',
  notes        VARCHAR(255)  NOT NULL DEFAULT '',
  created_by   INT UNSIGNED  NULL,
  created_at   DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_payments_member (member_id, paid_on),
  KEY ix_payments_syndicate (syndicate_id, paid_on),
  CONSTRAINT fk_payments_member FOREIGN KEY (member_id)
    REFERENCES members (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Balance ledger - append-only audit trail behind every balance change.
-- `bucket` says which balance moved; `amount` is signed.
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS ledger (
  id            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  member_id     INT UNSIGNED  NOT NULL,
  syndicate_id  INT UNSIGNED  NOT NULL,
  bucket        ENUM('contributions','winnings') NOT NULL,
  entry_type    ENUM('payment','winnings_share','subscription','opening','adjustment','reversal') NOT NULL,
  amount        DECIMAL(12,2) NOT NULL,
  balance_after DECIMAL(12,2) NOT NULL,
  -- 'draw' is a share of winnings, 'draw_cost' is the ticket charge. They are
  -- kept apart so reversing one never touches the other.
  source_type   ENUM('payment','draw','draw_cost','manual') NOT NULL DEFAULT 'manual',
  source_id     INT UNSIGNED  NULL,
  description   VARCHAR(255)  NOT NULL DEFAULT '',
  created_by    INT UNSIGNED  NULL,
  created_at    DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_ledger_member (member_id, created_at),
  KEY ix_ledger_source (source_type, source_id),
  CONSTRAINT fk_ledger_member FOREIGN KEY (member_id)
    REFERENCES members (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Outbound email log
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS email_log (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  draw_id    INT UNSIGNED NULL,
  member_id  INT UNSIGNED NULL,
  to_email   VARCHAR(190) NOT NULL,
  subject    VARCHAR(255) NOT NULL,
  email_type VARCHAR(40)  NOT NULL,
  status     ENUM('sent','failed') NOT NULL,
  error      TEXT         NULL,
  created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY ix_email_log_draw (draw_id),
  KEY ix_email_log_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- --------------------------------------------------------------------------
-- Small key/value store (schema version, last cron run, ...)
-- --------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_meta (
  meta_key   VARCHAR(64) NOT NULL,
  meta_value TEXT        NULL,
  updated_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (meta_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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