-- ============================================================
--  Rewards System Database
--  Import via phpMyAdmin or: mysql -u root < rewards.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS `rewards_db`
  DEFAULT CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `rewards_db`;

-- ─── Users ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id`         INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  `username`   VARCHAR(50)      NOT NULL,
  `password`   VARCHAR(255)     NOT NULL,
  `full_name`  VARCHAR(100)     NOT NULL DEFAULT '',
  `role`       ENUM('admin','cashier') NOT NULL DEFAULT 'cashier',
  `status`     ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Members ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `members` (
  `id`            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `card_code`     VARCHAR(60)   NOT NULL,
  `name`          VARCHAR(120)  DEFAULT NULL,
  `address`       TEXT          DEFAULT NULL,
  `phone`         VARCHAR(25)   DEFAULT NULL,
  `qr_data`       TEXT          DEFAULT NULL,
  `barcode_data`  TEXT          DEFAULT NULL,
  `points`        INT           NOT NULL DEFAULT 0,
  `status`        ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `registered_at` TIMESTAMP     NULL DEFAULT NULL,
  `created_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_card_code` (`card_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Transactions ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `transactions` (
  `id`         INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  `member_id`  INT UNSIGNED    NOT NULL,
  `amount`     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
  `points`     INT             NOT NULL DEFAULT 0,
  `type`       ENUM('earn','redeem') NOT NULL DEFAULT 'earn',
  `notes`      TEXT            DEFAULT NULL,
  `cashier_id` INT UNSIGNED    DEFAULT NULL,
  `created_at` TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_type` (`type`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_txn_member` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Settings ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `settings` (
  `id`            INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `setting_key`   VARCHAR(60)   NOT NULL,
  `setting_value` MEDIUMTEXT   NOT NULL DEFAULT '',
  `setting_label` VARCHAR(120)  NOT NULL DEFAULT '',
  `updated_at`    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ─── Seed: Default Settings ───────────────────────────────
INSERT INTO `settings` (`setting_key`, `setting_value`, `setting_label`) VALUES
('store_name',      'My Rewards Store',  'Store Name'),
('peso_per_point',  '100',               'Pesos Required to Earn 1 Point (₱)'),
('point_peso_value','1',                 'Peso Value Per Point When Redeeming (₱)'),
('min_redeem',      '50',               'Minimum Points Required to Redeem'),
('expiry_days',     '365',              'Points Expiry in Days (0 = never)'),
('card_prefix',     'RWRD',             'Card Code Prefix'),
('card_counter',          '0',                     'Internal Card Counter (auto)'),
('card_counter_date',     '',                      'Date of last counter reset (auto)'),
-- Card Design Settings
('card_bg_type',          'gradient',              'Card Background Type: gradient or image'),
('card_gradient_dir',     '135deg',                'Gradient Direction'),
('card_gradient_color1',  '#1e3a8a',               'Gradient Color 1 (start)'),
('card_gradient_color2',  '#1d4ed8',               'Gradient Color 2 (middle)'),
('card_gradient_color3',  '#0ea5e9',               'Gradient Color 3 (end)'),
('card_text_color',       '#ffffff',               'Card Text Color'),
('card_accent_color',     '#facc15',               'Card Accent / Logo Color'),
('card_bg_image',         '',                      'Card Background Image (base64 or path)'),
('card_logo_icon',        'star-fill',             'Bootstrap Icon name for card logo'),
('card_show_shimmer',     '1',                     'Show glossy shimmer overlay (1/0)'),
('card_label_text',       'MEMBER CARD',           'Card label text (bottom left)'),
('card_subtitle_text',    'REWARDS MEMBER',        'Card subtitle text'),
('card_qr_text',          '',                      'Card QR Code Text (top-left chip replacement — e.g. store URL). Leave blank to use card code.');

-- ─── Seed: Default Users ──────────────────────────────────
-- Passwords: admin → admin123 | cashier → cashier123
INSERT INTO `users` (`username`, `password`, `full_name`, `role`) VALUES
('admin',   '$2y$10$zvHYgYqj35VRiUwh7lHGFOAJM6WMcJAmkwrwRrKIFJng1bv2/mJju', 'Administrator', 'admin'),
('cashier', '$2y$10$bXFqgG/SZbROG35DnkZLcuF5oBUYZX7R5mp.Ozd/a7XVfMcAIRLfy', 'Cashier',       'cashier');
