-- ============================================================
-- Migration 002: Google OAuth, Avatar, Extras, Admin Notifs
-- ============================================================

-- ── 1. Add Google OAuth & avatar columns to users ────────────

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS google_id  VARCHAR(100) NULL DEFAULT NULL AFTER email_verified_at,
  ADD COLUMN IF NOT EXISTS avatar_url VARCHAR(500) NULL DEFAULT NULL AFTER google_id;

-- Index for fast Google ID lookup
ALTER TABLE users
  ADD INDEX IF NOT EXISTS idx_users_google_id (google_id);

-- ── 2. Extras settings table ─────────────────────────────────

CREATE TABLE IF NOT EXISTS extras_settings (
  id           INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  extra_key    VARCHAR(80)   NOT NULL,
  is_free      TINYINT(1)    NOT NULL DEFAULT 1,
  credit_cost  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  is_enabled   TINYINT(1)    NOT NULL DEFAULT 1,
  updated_at   TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  UNIQUE KEY   uq_extra_key (extra_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed default: IMEI checker free
INSERT IGNORE INTO extras_settings (extra_key, is_free, credit_cost, is_enabled)
VALUES ('imei_checker', 1, 0.00, 1);

-- ── 3. Extras usage log ───────────────────────────────────────

CREATE TABLE IF NOT EXISTS extras_log (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    INT UNSIGNED    NOT NULL,
  type       VARCHAR(80)     NOT NULL,
  input      VARCHAR(255)    NOT NULL DEFAULT '',
  result     JSON            NULL,
  cost       DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_extras_log_user  (user_id),
  KEY idx_extras_log_type  (type),
  KEY idx_extras_log_date  (created_at),
  CONSTRAINT fk_extras_log_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 4. Admin notifications table ──────────────────────────────

CREATE TABLE IF NOT EXISTS admin_notifications (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  type       VARCHAR(80)     NOT NULL,
  title      VARCHAR(255)    NOT NULL,
  body       TEXT            NOT NULL,
  data       JSON            NULL,
  is_read    TINYINT(1)      NOT NULL DEFAULT 0,
  created_at TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_admin_notif_type    (type),
  KEY idx_admin_notif_is_read (is_read),
  KEY idx_admin_notif_date    (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 5. Wallet default currency setting ───────────────────────
-- Ensure 'currency' setting exists defaulting to USD

INSERT IGNORE INTO settings (`key`, `value`, `group`, `type`)
VALUES ('currency', 'USD', 'payments', 'string');

-- ── 6. PIX fee setting ────────────────────────────────────────

INSERT IGNORE INTO settings (`key`, `value`, `group`, `type`, `label`)
VALUES ('pix_fee_percent', '0.00', 'payments', 'decimal', 'Taxa PIX (%)');

-- ── 7. IMEI checker service ID setting ───────────────────────

INSERT IGNORE INTO settings (`key`, `value`, `group`, `type`, `label`)
VALUES ('imei_checker_service_id', '1', 'extras', 'string', 'iFreeiCloud Service ID para IMEI');