-- Enamel Wallets Database Schema
-- Comprehensive Nigerian Fintech Database Design

-- Enable Row Level Security
ALTER DATABASE postgres SET "app.jwt_secret" TO 'your-jwt-secret-here';

-- Users table (extends Supabase auth.users)
CREATE TABLE public.users (
  id UUID REFERENCES auth.users ON DELETE CASCADE PRIMARY KEY,
  email TEXT UNIQUE NOT NULL,
  phone TEXT UNIQUE,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  date_of_birth DATE,
  address JSONB, -- {street, city, state, country, postal_code}
  
  -- KYC Information
  bvn TEXT,
  nin TEXT,
  id_type TEXT CHECK (id_type IN ('national_id', 'passport', 'drivers_license', 'voters_card')),
  id_number TEXT,
  kyc_status TEXT DEFAULT 'pending' CHECK (kyc_status IN ('pending', 'verified', 'rejected')),
  kyc_tier INTEGER DEFAULT 1 CHECK (kyc_tier IN (1, 2, 3)),
  verification_documents JSONB,
  
  -- Account Information
  account_number TEXT UNIQUE NOT NULL, -- 10-digit Enamel account number
  membership_number TEXT UNIQUE NOT NULL, -- EMW-YYYY-XXXXXX format
  account_status TEXT DEFAULT 'active' CHECK (account_status IN ('active', 'suspended', 'closed')),
  
  -- Role and Permissions
  role TEXT DEFAULT 'customer' CHECK (role IN ('customer', 'staff', 'admin')),
  permissions JSONB DEFAULT '[]'::jsonb,
  
  -- Settings
  notification_preferences JSONB DEFAULT '{
    "email": true,
    "sms": true,
    "push": true,
    "transaction_alerts": true,
    "marketing": false
  }'::jsonb,
  
  -- Transaction Limits (in kobo - 1 Naira = 100 kobo)
  daily_limit BIGINT DEFAULT 5000000, -- ₦50,000 in kobo
  monthly_limit BIGINT DEFAULT 100000000, -- ₦1,000,000 in kobo
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  last_login_at TIMESTAMPTZ,
  email_verified_at TIMESTAMPTZ,
  phone_verified_at TIMESTAMPTZ
);

-- Wallets table (each user can have multiple wallets)
CREATE TABLE public.wallets (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
  
  -- Wallet Details
  wallet_type TEXT NOT NULL CHECK (wallet_type IN ('spending', 'daily_savings', 'property_savings', 'custom_wallet', 'group_savings')),
  name TEXT NOT NULL, -- User-defined name
  account_number TEXT UNIQUE NOT NULL, -- Unique account number for this wallet
  
  -- Balance and Goals (in kobo)
  balance BIGINT DEFAULT 0 NOT NULL CHECK (balance >= 0),
  target_amount BIGINT DEFAULT 0,
  
  -- Savings Configuration
  daily_contribution BIGINT DEFAULT 0,
  monthly_contribution BIGINT DEFAULT 0,
  auto_save_enabled BOOLEAN DEFAULT false,
  
  -- Lock Settings
  is_locked BOOLEAN DEFAULT false,
  unlock_date DATE,
  early_withdrawal_penalty DECIMAL(5,2) DEFAULT 0.00,
  
  -- Group Savings Specific
  group_id UUID REFERENCES public.groups(id) ON DELETE SET NULL,
  
  -- Status
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'frozen', 'closed')),
  
  -- Metadata
  metadata JSONB DEFAULT '{}'::jsonb,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Groups table (for group savings - Ajo/Esusu)
CREATE TABLE public.groups (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  description TEXT,
  
  -- Group Settings
  member_limit INTEGER DEFAULT 20 CHECK (member_limit BETWEEN 2 AND 50),
  contribution_amount BIGINT NOT NULL CHECK (contribution_amount > 0),
  contribution_frequency TEXT DEFAULT 'monthly' CHECK (contribution_frequency IN ('weekly', 'monthly')),
  payout_order TEXT DEFAULT 'random' CHECK (payout_order IN ('random', 'first_come', 'lottery')),
  
  -- Group Rules
  start_date DATE NOT NULL,
  duration_months INTEGER NOT NULL CHECK (duration_months > 0),
  late_payment_penalty DECIMAL(5,2) DEFAULT 0.05, -- 5% default penalty
  
  -- Status
  status TEXT DEFAULT 'recruiting' CHECK (status IN ('recruiting', 'active', 'completed', 'cancelled')),
  
  -- Admin
  created_by UUID REFERENCES public.users(id) NOT NULL,
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Group Members table
CREATE TABLE public.group_members (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  group_id UUID REFERENCES public.groups(id) ON DELETE CASCADE NOT NULL,
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
  
  -- Member Details
  position INTEGER, -- Payout position (1st to receive, 2nd, etc.)
  joined_at TIMESTAMPTZ DEFAULT NOW(),
  status TEXT DEFAULT 'active' CHECK (status IN ('active', 'defaulted', 'completed')),
  
  -- Contribution Tracking
  total_contributed BIGINT DEFAULT 0,
  contributions_made INTEGER DEFAULT 0,
  missed_payments INTEGER DEFAULT 0,
  
  -- Payout Tracking
  payout_received BOOLEAN DEFAULT false,
  payout_amount BIGINT DEFAULT 0,
  payout_date TIMESTAMPTZ,
  
  UNIQUE(group_id, user_id),
  UNIQUE(group_id, position)
);

-- Transactions table (comprehensive transaction log)
CREATE TABLE public.transactions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  
  -- Transaction Identification
  reference TEXT UNIQUE NOT NULL, -- EMW-TXN-TIMESTAMP format
  external_reference TEXT, -- Third-party reference (Paystack, etc.)
  
  -- Parties Involved
  user_id UUID REFERENCES public.users(id) NOT NULL,
  from_wallet_id UUID REFERENCES public.wallets(id),
  to_wallet_id UUID REFERENCES public.wallets(id),
  
  -- Transaction Details
  type TEXT NOT NULL CHECK (type IN (
    'deposit', 'withdrawal', 'transfer', 'savings_contribution', 
    'group_contribution', 'group_payout', 'bill_payment', 'airtime', 
    'data', 'electricity', 'water', 'cable_tv', 'internet',
    'penalty', 'interest', 'commission', 'refund', 'reversal'
  )),
  
  -- Amounts (in kobo)
  amount BIGINT NOT NULL CHECK (amount > 0),
  fee BIGINT DEFAULT 0 CHECK (fee >= 0),
  total_amount BIGINT GENERATED ALWAYS AS (amount + fee) STORED,
  
  -- Status Tracking
  status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'cancelled', 'reversed')),
  
  -- Additional Information
  description TEXT,
  notes TEXT,
  metadata JSONB DEFAULT '{}'::jsonb, -- Store additional transaction data
  
  -- External Integration
  payment_method TEXT, -- 'bank_transfer', 'card', 'ussd', 'mobile_money', etc.
  gateway_response JSONB, -- Store gateway API responses
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  completed_at TIMESTAMPTZ,
  
  -- Ensure valid wallet combinations
  CONSTRAINT valid_wallet_transfer CHECK (
    (type = 'transfer' AND from_wallet_id IS NOT NULL AND to_wallet_id IS NOT NULL) OR
    (type != 'transfer')
  )
);

-- Payments table (bill payments, airtime, etc.)
CREATE TABLE public.payments (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  transaction_id UUID REFERENCES public.transactions(id) NOT NULL,
  user_id UUID REFERENCES public.users(id) NOT NULL,
  
  -- Payment Details
  service_type TEXT NOT NULL CHECK (service_type IN (
    'airtime', 'data', 'electricity', 'water', 'cable_tv', 
    'internet', 'school_fees', 'insurance', 'loan_repayment'
  )),
  provider TEXT NOT NULL, -- MTN, Glo, AEDC, DSTV, etc.
  
  -- Customer Information
  customer_identifier TEXT NOT NULL, -- Phone number, meter number, etc.
  customer_name TEXT,
  
  -- Service Specific
  product_code TEXT, -- Data plan codes, electricity tariff, etc.
  units DECIMAL(10,2), -- Electricity units, data MB, etc.
  
  -- Payment Status
  provider_reference TEXT,
  provider_response JSONB,
  
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Notifications table
CREATE TABLE public.notifications (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID REFERENCES public.users(id) ON DELETE CASCADE NOT NULL,
  
  -- Notification Details
  type TEXT NOT NULL CHECK (type IN (
    'transaction', 'payment', 'savings_goal', 'group_activity', 
    'kyc_update', 'security_alert', 'promotional', 'system'
  )),
  title TEXT NOT NULL,
  message TEXT NOT NULL,
  
  -- Status
  read BOOLEAN DEFAULT false,
  read_at TIMESTAMPTZ,
  
  -- Delivery Channels
  channels TEXT[] DEFAULT '{app}'::text[], -- app, email, sms, push
  delivery_status JSONB DEFAULT '{}'::jsonb,
  
  -- Related Entity
  related_entity_type TEXT, -- transaction, group, wallet, etc.
  related_entity_id UUID,
  
  -- Action
  action_url TEXT,
  action_data JSONB,
  
  -- Metadata
  priority TEXT DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
  expires_at TIMESTAMPTZ,
  
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Admin Logs table (audit trail)
CREATE TABLE public.admin_logs (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  admin_id UUID REFERENCES public.users(id) NOT NULL,
  
  -- Action Details
  action TEXT NOT NULL, -- create_user, suspend_account, process_kyc, etc.
  entity_type TEXT NOT NULL, -- user, transaction, group, etc.
  entity_id UUID,
  
  -- Changes
  old_values JSONB,
  new_values JSONB,
  
  -- Context
  ip_address INET,
  user_agent TEXT,
  reason TEXT,
  
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- System Settings table
CREATE TABLE public.system_settings (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  description TEXT,
  updated_by UUID REFERENCES public.users(id),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create Indexes for Performance
CREATE INDEX idx_users_email ON public.users(email);
CREATE INDEX idx_users_phone ON public.users(phone);
CREATE INDEX idx_users_account_number ON public.users(account_number);
CREATE INDEX idx_users_membership_number ON public.users(membership_number);

CREATE INDEX idx_wallets_user_id ON public.wallets(user_id);
CREATE INDEX idx_wallets_type ON public.wallets(wallet_type);
CREATE INDEX idx_wallets_account_number ON public.wallets(account_number);

CREATE INDEX idx_transactions_user_id ON public.transactions(user_id);
CREATE INDEX idx_transactions_type ON public.transactions(type);
CREATE INDEX idx_transactions_status ON public.transactions(status);
CREATE INDEX idx_transactions_created_at ON public.transactions(created_at);
CREATE INDEX idx_transactions_reference ON public.transactions(reference);

CREATE INDEX idx_notifications_user_id ON public.notifications(user_id);
CREATE INDEX idx_notifications_read ON public.notifications(read);
CREATE INDEX idx_notifications_created_at ON public.notifications(created_at);

-- Row Level Security (RLS) Policies
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.wallets ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.transactions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notifications ENABLE ROW LEVEL SECURITY;

-- Users can only see their own data
CREATE POLICY "Users can view own profile" ON public.users
  FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON public.users
  FOR UPDATE USING (auth.uid() = id);

-- Wallet policies
CREATE POLICY "Users can view own wallets" ON public.wallets
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can update own wallets" ON public.wallets
  FOR UPDATE USING (auth.uid() = user_id);

-- Transaction policies
CREATE POLICY "Users can view own transactions" ON public.transactions
  FOR SELECT USING (auth.uid() = user_id);

-- Notification policies
CREATE POLICY "Users can view own notifications" ON public.notifications
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can update own notifications" ON public.notifications
  FOR UPDATE USING (auth.uid() = user_id);

-- Admin policies (admins can see everything)
CREATE POLICY "Admins can view all users" ON public.users
  FOR ALL USING (
    EXISTS (
      SELECT 1 FROM public.users 
      WHERE id = auth.uid() AND role IN ('admin', 'staff')
    )
  );

-- Insert default system settings
INSERT INTO public.system_settings (key, value, description) VALUES
('transaction_fees', '{"transfer": 0, "bill_payment": 100, "withdrawal": 0}', 'Transaction fees in kobo'),
('daily_limits', '{"tier1": 5000000, "tier2": 10000000, "tier3": 50000000}', 'Daily transaction limits by KYC tier in kobo'),
('group_settings', '{"max_members": 20, "min_contribution": 100000}', 'Group savings configuration'),
('maintenance_mode', 'false', 'System maintenance mode flag'),
('supported_banks', '[]', 'List of supported banks for transfers'),
('api_keys', '{"paystack": "", "prembly": "", "termii": ""}', 'Third-party API keys (encrypted)');

-- Functions for account number generation (already implemented in your utils)
-- These would be called from your application logic

-- Triggers for updated_at timestamps
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_wallets_updated_at BEFORE UPDATE ON public.wallets FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_transactions_updated_at BEFORE UPDATE ON public.transactions FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_groups_updated_at BEFORE UPDATE ON public.groups FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();