-- RealLifeStores Database Schema

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Users (community members)
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY,
  telegram_id BIGINT UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  telegram_username VARCHAR(100),
  referral_code VARCHAR(20) UNIQUE NOT NULL DEFAULT substr(md5(random()::text), 1, 8),
  referred_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
  points INTEGER NOT NULL DEFAULT 0,
  total_sales INTEGER NOT NULL DEFAULT 0,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Verification Codes
CREATE TABLE IF NOT EXISTS verification_codes (
  id SERIAL PRIMARY KEY,
  code VARCHAR(6) UNIQUE NOT NULL,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  created_by INTEGER REFERENCES users(id) NOT NULL, -- admin who created it
  used_at TIMESTAMPTZ,
  expires_at TIMESTAMPTZ DEFAULT (NOW() + INTERVAL '30 days'),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Products
CREATE TABLE IF NOT EXISTS products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price INTEGER NOT NULL, -- in smallest currency unit (kobo)
  points_value INTEGER NOT NULL DEFAULT 0,
  image_url TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Orders
CREATE TABLE IF NOT EXISTS orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) NOT NULL,
  status VARCHAR(50) DEFAULT 'pending', -- pending | paid | cancelled
  total_amount INTEGER NOT NULL,
  payment_ref VARCHAR(255),
  payment_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Order Items
CREATE TABLE IF NOT EXISTS order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER REFERENCES products(id),
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price INTEGER NOT NULL,
  points_earned INTEGER NOT NULL DEFAULT 0
);

-- Points Transactions (full audit trail)
CREATE TABLE IF NOT EXISTS points_transactions (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) NOT NULL,
  amount INTEGER NOT NULL, -- positive = earned, negative = redeemed
  type VARCHAR(50) NOT NULL, -- sale | referral_l1 | referral_l2 | referral_l3 | admin
  source_order_id INTEGER REFERENCES orders(id) ON DELETE SET NULL,
  note TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_users_telegram_id ON users(telegram_id);
CREATE INDEX IF NOT EXISTS idx_users_referred_by ON users(referred_by);
CREATE INDEX IF NOT EXISTS idx_users_referral_code ON users(referral_code);
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_points_tx_user_id ON points_transactions(user_id);

-- Seed some products
INSERT INTO products (name, description, price, points_value) VALUES
  ('Starter Kit', 'Perfect entry package for new members', 250000, 25),
  ('Premium Bundle', 'Our best-selling mid-tier package', 500000, 50),
  ('Gold Package', 'High-value package for serious earners', 1500000, 150),
  ('Elite Pack', 'Top-tier package for network leaders', 3000000, 300)
ON CONFLICT DO NOTHING;