-- =====================================================================
-- AURA LIVE — Migration 004: Gifts, coin packages, payments
-- =====================================================================

CREATE TABLE IF NOT EXISTS gift_categories (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(60) NOT NULL,
  slug       VARCHAR(60) NOT NULL,
  icon_url   VARCHAR(500) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active  TINYINT(1) NOT NULL DEFAULT 1,
  UNIQUE KEY uq_gift_cat_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gifts (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(80) NOT NULL,
  category_id   BIGINT UNSIGNED NOT NULL,
  price_coins   INT UNSIGNED NOT NULL,
  icon_url      VARCHAR(500) NULL,
  animation_url VARCHAR(500) NULL,
  is_active     TINYINT(1) NOT NULL DEFAULT 1,
  is_premium    TINYINT(1) NOT NULL DEFAULT 0,
  sort_order    INT NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_gifts_category (category_id, is_active),
  CONSTRAINT fk_gifts_category FOREIGN KEY (category_id) REFERENCES gift_categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gift_transactions (
  id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uuid         CHAR(36) NOT NULL,
  gift_id      BIGINT UNSIGNED NOT NULL,
  sender_id    BIGINT UNSIGNED NOT NULL,
  recipient_id BIGINT UNSIGNED NOT NULL,
  room_id      BIGINT UNSIGNED NULL,
  quantity     INT UNSIGNED NOT NULL DEFAULT 1,
  unit_price   INT UNSIGNED NOT NULL,
  total_coins  INT UNSIGNED NOT NULL,
  created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_gift_tx_uuid (uuid),
  KEY idx_gift_tx_sender (sender_id, created_at),
  KEY idx_gift_tx_recipient (recipient_id, created_at),
  KEY idx_gift_tx_room (room_id, created_at),
  CONSTRAINT fk_gift_tx_gift FOREIGN KEY (gift_id) REFERENCES gifts(id),
  CONSTRAINT fk_gift_tx_sender FOREIGN KEY (sender_id) REFERENCES users(id),
  CONSTRAINT fk_gift_tx_recipient FOREIGN KEY (recipient_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS coin_packages (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(80) NOT NULL,
  coins       INT UNSIGNED NOT NULL,
  bonus_coins INT UNSIGNED NOT NULL DEFAULT 0,
  price_inr   DECIMAL(10,2) NOT NULL,
  is_active   TINYINT(1) NOT NULL DEFAULT 1,
  is_popular  TINYINT(1) NOT NULL DEFAULT 0,
  sort_order  INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_orders (
  id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_number     VARCHAR(40) NOT NULL,
  user_id          BIGINT UNSIGNED NOT NULL,
  coin_package_id  BIGINT UNSIGNED NULL,
  coins            INT UNSIGNED NOT NULL,
  amount_inr       DECIMAL(10,2) NOT NULL,
  provider         VARCHAR(30) NOT NULL,
  provider_order_id VARCHAR(100) NULL,
  status           ENUM('created','pending','paid','failed','cancelled','refunded') NOT NULL DEFAULT 'created',
  credited_at      DATETIME NULL,
  created_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_orders_number (order_number),
  UNIQUE KEY uq_orders_provider (provider, provider_order_id),
  KEY idx_orders_user (user_id, created_at),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id),
  CONSTRAINT fk_orders_package FOREIGN KEY (coin_package_id) REFERENCES coin_packages(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_events (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   BIGINT UNSIGNED NULL,
  provider   VARCHAR(30) NOT NULL,
  event_id   VARCHAR(120) NOT NULL,
  type       VARCHAR(60) NOT NULL,
  payload    JSON NULL,
  processed  TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_payment_events (provider, event_id),
  KEY idx_payment_events_order (order_id),
  CONSTRAINT fk_payment_events_order FOREIGN KEY (order_id) REFERENCES payment_orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS refund_records (
  id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id      BIGINT UNSIGNED NOT NULL,
  provider_refund_id VARCHAR(100) NULL,
  amount_inr    DECIMAL(10,2) NOT NULL,
  reason        VARCHAR(255) NULL,
  status        ENUM('requested','processed','failed') NOT NULL DEFAULT 'requested',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_refunds_order FOREIGN KEY (order_id) REFERENCES payment_orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;