-- SubGuard — MySQL schema (multi-tenant Telegram subscription manager)
-- Import via cPanel > phpMyAdmin, or: mysql -u USER -p DB < db/schema.sql
-- Requires MySQL 5.7+ / MariaDB 10.2+ (utf8mb4).

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- A "group" is one Telegram group/instance the bot manages.
CREATE TABLE IF NOT EXISTS `groups` (
  `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_chat_id`  BIGINT          NOT NULL,            -- negative for supergroups
  `title`             VARCHAR(255)    NOT NULL DEFAULT '',
  `owner_user_id`     BIGINT          DEFAULT NULL,        -- Telegram user id of owner
  `timezone`          VARCHAR(64)     NOT NULL DEFAULT 'Africa/Lagos',
  `currency`          VARCHAR(8)      NOT NULL DEFAULT 'NGN',
  `roster_message_id` BIGINT          DEFAULT NULL,        -- pinned roster message to edit
  `welcome_enabled`   TINYINT(1)      NOT NULL DEFAULT 1,
  -- Members who join but never activate: nudge, then remove.
  `pending_nudge_days`  VARCHAR(64)   NOT NULL DEFAULT '3,6', -- CSV of days-after-join to nudge
  `pending_remove_days` INT           NOT NULL DEFAULT 7,     -- remove after N days unactivated; 0 = never
  -- per-group payment credentials (override global config); NULL = use global
  `paystack_secret`   VARCHAR(255)    DEFAULT NULL,
  `paystack_public`   VARCHAR(255)    DEFAULT NULL,
  `flw_secret`        VARCHAR(255)    DEFAULT NULL,
  `flw_public`        VARCHAR(255)    DEFAULT NULL,
  `flw_secret_hash`   VARCHAR(255)    DEFAULT NULL,
  `active`            TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_chat` (`telegram_chat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Telegram users who may administer a group (beyond the owner).
CREATE TABLE IF NOT EXISTS `group_admins` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id`         BIGINT UNSIGNED NOT NULL,
  `telegram_user_id` BIGINT          NOT NULL,
  `username`         VARCHAR(255)    DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_group_user` (`group_id`,`telegram_user_id`),
  CONSTRAINT `fk_ga_group` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Subscription plans, per group.
CREATE TABLE IF NOT EXISTS `plans` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id`         BIGINT UNSIGNED NOT NULL,
  `name`             VARCHAR(64)     NOT NULL,
  `duration_days`    INT             NOT NULL,             -- 7, 30, 365, or custom
  `price`            DECIMAL(12,2)   NOT NULL DEFAULT 0,
  `reminder_days`    VARCHAR(64)     NOT NULL DEFAULT '3,1',-- CSV of days-before to remind
  `active`           TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `k_group` (`group_id`),
  CONSTRAINT `fk_plan_group` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per member per group. Holds their CURRENT subscription state.
CREATE TABLE IF NOT EXISTS `members` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id`         BIGINT UNSIGNED NOT NULL,
  `telegram_user_id` BIGINT          NOT NULL,
  `username`         VARCHAR(255)    DEFAULT NULL,
  `full_name`        VARCHAR(255)    DEFAULT NULL,
  `plan_id`          BIGINT UNSIGNED DEFAULT NULL,
  `status`           ENUM('pending','active','due_soon','expired','removed') NOT NULL DEFAULT 'pending',
  `activated_at`     DATETIME        DEFAULT NULL,
  `expires_at`       DATETIME        DEFAULT NULL,
  `late_fee_accrued` DECIMAL(12,2)   NOT NULL DEFAULT 0,
  `last_reminder_day`INT             DEFAULT NULL,         -- which "days-before" bucket was last sent
  `last_pending_nudge` INT           DEFAULT NULL,         -- which "days-since-join" nudge was last sent
  `joined_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_group_member` (`group_id`,`telegram_user_id`),
  KEY `k_status` (`group_id`,`status`),
  KEY `k_expires` (`expires_at`),
  CONSTRAINT `fk_mem_group` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mem_plan`  FOREIGN KEY (`plan_id`)  REFERENCES `plans`(`id`)  ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-group late-penalty rule (single row per group).
CREATE TABLE IF NOT EXISTS `penalty_rules` (
  `group_id`          BIGINT UNSIGNED NOT NULL,
  `enabled`           TINYINT(1)      NOT NULL DEFAULT 1,
  `grace_days`        INT             NOT NULL DEFAULT 2,
  `fee_per_day`       DECIMAL(12,2)   NOT NULL DEFAULT 0,
  `remove_after_days` INT             NOT NULL DEFAULT 5,   -- days overdue before auto-remove; 0 = never
  PRIMARY KEY (`group_id`),
  CONSTRAINT `fk_pen_group` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Payment attempts / records.
CREATE TABLE IF NOT EXISTS `payments` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id`    BIGINT UNSIGNED NOT NULL,
  `member_id`   BIGINT UNSIGNED DEFAULT NULL,
  `plan_id`     BIGINT UNSIGNED DEFAULT NULL,
  `provider`    ENUM('paystack','flutterwave','manual') NOT NULL,
  `reference`   VARCHAR(128)    NOT NULL,
  `amount`      DECIMAL(12,2)   NOT NULL DEFAULT 0,
  `currency`    VARCHAR(8)      NOT NULL DEFAULT 'NGN',
  `status`      ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
  `raw`         TEXT            DEFAULT NULL,               -- provider payload (JSON)
  `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `paid_at`     DATETIME        DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ref` (`provider`,`reference`),
  KEY `k_group` (`group_id`),
  CONSTRAINT `fk_pay_group` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Immutable log of subscription events (activations, renewals, expiries).
CREATE TABLE IF NOT EXISTS `subscription_events` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `group_id`   BIGINT UNSIGNED NOT NULL,
  `member_id`  BIGINT UNSIGNED DEFAULT NULL,
  `event`      VARCHAR(32)     NOT NULL,   -- activated | renewed | reminded | expired | penalized | removed
  `detail`     VARCHAR(255)    DEFAULT NULL,
  `created_at` DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `k_group` (`group_id`),
  KEY `k_member` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;