-- SmartPay Loyalty Bridge MVP schema
-- MySQL / MariaDB

CREATE TABLE IF NOT EXISTS loyalty_customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(20) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL DEFAULT '',
  email VARCHAR(120) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loyalty_accounts (
  customer_id INT PRIMARY KEY,
  tier VARCHAR(30) NOT NULL DEFAULT 'Silver',
  points_balance INT NOT NULL DEFAULT 0,
  lifetime_points INT NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_loy_acc_customer FOREIGN KEY (customer_id) REFERENCES loyalty_customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loyalty_rules (
  id INT AUTO_INCREMENT PRIMARY KEY,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  points_per_kes DECIMAL(10,4) NOT NULL DEFAULT 0.0200, -- e.g. 0.02 => 1 point per KES 50
  min_spend DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  max_points_per_sale INT NULL,
  note VARCHAR(255) NOT NULL DEFAULT 'Default rule',
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS loyalty_transactions (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  branch_code VARCHAR(20) NOT NULL DEFAULT '',
  cashier VARCHAR(80) NOT NULL DEFAULT '',
  pos_txn_no VARCHAR(60) NOT NULL DEFAULT '',
  sale_total DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  points_awarded INT NOT NULL DEFAULT 0,
  source VARCHAR(20) NOT NULL DEFAULT 'RMS',
  meta_json JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_loy_txn_customer (customer_id),
  KEY idx_loy_txn_pos (pos_txn_no),
  CONSTRAINT fk_loy_txn_customer FOREIGN KEY (customer_id) REFERENCES loyalty_customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS pos_receipt_notes (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  pos_txn_no VARCHAR(60) NOT NULL,
  note VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pos_txn (pos_txn_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default rule
INSERT INTO loyalty_rules (is_active, points_per_kes, min_spend, max_points_per_sale, note)
SELECT 1, 0.0200, 0.00, NULL, '1 point per KES 50'
WHERE NOT EXISTS (SELECT 1 FROM loyalty_rules);
