-- =============================================
-- SMA Katolik St. Louis 1 Surabaya Ticketing
-- Complete Database Schema v2 (fixed FK order)
-- =============================================

BEGIN;

-- ===== CLASSES (School) =====

CREATE TABLE IF NOT EXISTS classes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_classes_created_by ON classes(created_by);

-- ===== PROMOS (defined BEFORE orders because orders FK references them) =====

CREATE TABLE IF NOT EXISTS promos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  code VARCHAR(50) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  promo_type VARCHAR(50) NOT NULL CHECK (promo_type IN ('percentage', 'fixed', 'free')),
  value DECIMAL(12, 2) NOT NULL,
  min_purchase DECIMAL(12, 2) DEFAULT 0,
  max_discount DECIMAL(12, 2),
  usage_limit INT,
  usage_count INT DEFAULT 0,
  per_user_limit INT DEFAULT 1,
  event_id UUID REFERENCES events(id) ON DELETE CASCADE,
  valid_from TIMESTAMP NOT NULL,
  valid_until TIMESTAMP NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_promos_code ON promos(code);
CREATE INDEX IF NOT EXISTS idx_promos_event ON promos(event_id);
CREATE INDEX IF NOT EXISTS idx_promos_active ON promos(is_active, valid_from, valid_until);

-- ===== DOMAIN PROMOS (defined BEFORE orders) =====

CREATE TABLE IF NOT EXISTS domain_promos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  domain VARCHAR(255) NOT NULL,
  promo_type VARCHAR(50) NOT NULL CHECK (promo_type IN ('percentage', 'fixed', 'free')),
  value DECIMAL(12, 2) NOT NULL,
  max_discount DECIMAL(12, 2),
  event_id UUID REFERENCES events(id) ON DELETE CASCADE,
  description TEXT,
  is_active BOOLEAN DEFAULT TRUE,
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_domain_promos_domain ON domain_promos(domain);
CREATE INDEX IF NOT EXISTS idx_domain_promos_event ON domain_promos(event_id);

-- ===== EVENTS =====

CREATE TABLE IF NOT EXISTS events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organizer_id UUID NOT NULL REFERENCES users(id),
  name VARCHAR(255) NOT NULL,
  description TEXT,
  venue VARCHAR(255) NOT NULL,
  event_date TIMESTAMP NOT NULL,
  sale_start TIMESTAMP NOT NULL,
  sale_end TIMESTAMP NOT NULL,
  total_capacity INT NOT NULL,
  remaining_capacity INT NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'active', 'paused', 'closed', 'cancelled')),
  war_mode BOOLEAN DEFAULT FALSE,
  image_url TEXT,
  queue_max_tickets_per_person INT DEFAULT 4,
  queue_session_duration INT DEFAULT 5,
  queue_max_active_sessions INT DEFAULT 10,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_events_organizer ON events(organizer_id);
CREATE INDEX IF NOT EXISTS idx_events_date ON events(event_date);
CREATE INDEX IF NOT EXISTS idx_events_status ON events(status);

-- ===== EVENT IMAGES =====

CREATE TABLE IF NOT EXISTS event_images (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  image_url TEXT NOT NULL,
  display_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_event_images_event ON event_images(event_id);
CREATE INDEX IF NOT EXISTS idx_event_images_order ON event_images(event_id, display_order);

-- ===== EVENT CLASSES (many-to-many) =====

CREATE TABLE IF NOT EXISTS event_classes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE(event_id, class_id)
);

CREATE INDEX IF NOT EXISTS idx_event_classes_event ON event_classes(event_id);
CREATE INDEX IF NOT EXISTS idx_event_classes_class ON event_classes(class_id);

-- ===== TICKET CLASSES =====

CREATE TABLE IF NOT EXISTS ticket_classes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(12, 2) NOT NULL,
  total_capacity INT NOT NULL,
  remaining_capacity INT NOT NULL,
  sale_window_start TIMESTAMP NOT NULL,
  sale_window_end TIMESTAMP NOT NULL,
  seat_plan_image TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ticket_classes_event ON ticket_classes(event_id);

-- ===== QUEUE SYSTEM =====

CREATE TABLE IF NOT EXISTS queue_entries (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  position INT NOT NULL,
  ip_address VARCHAR(45),
  status VARCHAR(50) NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'active', 'expired', 'completed', 'cancelled')),
  entered_at TIMESTAMP DEFAULT NOW(),
  activated_at TIMESTAMP,
  expires_at TIMESTAMP,
  completed_at TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_queue_entries_event_user ON queue_entries(event_id, user_id);
CREATE INDEX IF NOT EXISTS idx_queue_entries_status ON queue_entries(status);
CREATE INDEX IF NOT EXISTS idx_queue_entries_position ON queue_entries(event_id, position);
CREATE INDEX IF NOT EXISTS idx_queue_entries_expires ON queue_entries(event_id, expires_at) WHERE status = 'active';

-- ===== ORDERS (FK promos/domain_promos resolved — tables defined above) =====

CREATE TABLE IF NOT EXISTS orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES events(id),
  ticket_class_id UUID NOT NULL REFERENCES ticket_classes(id),
  buyer_id UUID NOT NULL REFERENCES users(id),
  quantity INT NOT NULL,
  total_amount DECIMAL(12, 2) NOT NULL,
  buyer_name VARCHAR(255) NOT NULL,
  buyer_email VARCHAR(255) NOT NULL,
  buyer_phone VARCHAR(20) NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'cancelled', 'expired')),
  order_lock_id UUID,
  promo_id UUID REFERENCES promos(id),
  domain_promo_id UUID REFERENCES domain_promos(id),
  discount_amount DECIMAL(12, 2) DEFAULT 0,
  original_amount DECIMAL(12, 2),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_orders_buyer ON orders(buyer_id);
CREATE INDEX IF NOT EXISTS idx_orders_event ON orders(event_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_created ON orders(created_at);

-- ===== ORDER LOCKS =====

CREATE TABLE IF NOT EXISTS order_locks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  ticket_class_id UUID NOT NULL REFERENCES ticket_classes(id) ON DELETE CASCADE,
  quantity_locked INT NOT NULL,
  user_id UUID NOT NULL REFERENCES users(id),
  locked_at TIMESTAMP DEFAULT NOW(),
  expires_at TIMESTAMP NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'released', 'converted'))
);

CREATE INDEX IF NOT EXISTS idx_order_locks_event_class ON order_locks(event_id, ticket_class_id);
CREATE INDEX IF NOT EXISTS idx_order_locks_expires ON order_locks(expires_at);

-- ===== TICKETS =====

CREATE TABLE IF NOT EXISTS tickets (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  booking_code VARCHAR(50) UNIQUE NOT NULL,
  qr_code TEXT NOT NULL,
  ticket_class_name VARCHAR(255) NOT NULL,
  buyer_name VARCHAR(255) NOT NULL,
  buyer_email VARCHAR(255) NOT NULL,
  event_name VARCHAR(255) NOT NULL,
  event_date TIMESTAMP NOT NULL,
  status VARCHAR(50) NOT NULL DEFAULT 'unused' CHECK (status IN ('unused', 'used', 'voided')),
  used_at TIMESTAMP,
  used_by UUID REFERENCES users(id),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_tickets_booking_code ON tickets(booking_code);
CREATE INDEX IF NOT EXISTS idx_tickets_order ON tickets(order_id);
CREATE INDEX IF NOT EXISTS idx_tickets_status ON tickets(status);

-- ===== PROMO USAGES =====

CREATE TABLE IF NOT EXISTS promo_usages (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  promo_id UUID REFERENCES promos(id) ON DELETE CASCADE,
  domain_promo_id UUID REFERENCES domain_promos(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id),
  order_id UUID NOT NULL REFERENCES orders(id),
  discount_amount DECIMAL(12, 2) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  CONSTRAINT check_promo_type CHECK (
    (promo_id IS NOT NULL AND domain_promo_id IS NULL) OR
    (promo_id IS NULL AND domain_promo_id IS NOT NULL)
  )
);

CREATE INDEX IF NOT EXISTS idx_promo_usages_promo ON promo_usages(promo_id);
CREATE INDEX IF NOT EXISTS idx_promo_usages_user ON promo_usages(user_id);
CREATE INDEX IF NOT EXISTS idx_promo_usages_order ON promo_usages(order_id);

-- ===== WALI KELAS =====

CREATE TABLE IF NOT EXISTS wali_kelas_classes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  wali_kelas_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  class_id UUID NOT NULL REFERENCES classes(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT NOW(),
  UNIQUE (wali_kelas_id, class_id)
);

CREATE INDEX IF NOT EXISTS idx_wali_kelas_classes_wali ON wali_kelas_classes(wali_kelas_id);
CREATE INDEX IF NOT EXISTS idx_wali_kelas_classes_class ON wali_kelas_classes(class_id);

CREATE TABLE IF NOT EXISTS wali_kelas_orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  wali_kelas_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  student_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE RESTRICT,
  ticket_class_id UUID NOT NULL REFERENCES ticket_classes(id) ON DELETE RESTRICT,
  order_id UUID NOT NULL REFERENCES orders(id) ON DELETE RESTRICT,
  quantity INT NOT NULL CHECK (quantity > 0),
  notes TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_wali_kelas_orders_wali ON wali_kelas_orders(wali_kelas_id);
CREATE INDEX IF NOT EXISTS idx_wali_kelas_orders_student ON wali_kelas_orders(student_id);
CREATE INDEX IF NOT EXISTS idx_wali_kelas_orders_event ON wali_kelas_orders(event_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_wali_kelas_orders_order ON wali_kelas_orders(order_id);

-- ===== TICKETING EVENT ACCESS =====

CREATE TABLE IF NOT EXISTS ticketing_event_access (
  ticketing_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (ticketing_user_id, event_id)
);

CREATE INDEX IF NOT EXISTS idx_ticketing_event_access_user ON ticketing_event_access (ticketing_user_id);
CREATE INDEX IF NOT EXISTS idx_ticketing_event_access_event ON ticketing_event_access (event_id);

-- ===== SETTINGS (minimal — app config now lives in config.json) =====

CREATE TABLE IF NOT EXISTS settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  setting_key VARCHAR(255) UNIQUE NOT NULL,
  setting_value TEXT NOT NULL,
  description TEXT,
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_settings_key ON settings(setting_key);

-- ===== SYSTEM LOGS =====

CREATE TABLE IF NOT EXISTS system_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  log_type VARCHAR(50) NOT NULL,
  user_id UUID REFERENCES users(id),
  action VARCHAR(255) NOT NULL,
  resource_type VARCHAR(100),
  resource_id VARCHAR(255),
  status VARCHAR(50),
  details JSONB,
  ip_address VARCHAR(45),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_logs_type ON system_logs(log_type);
CREATE INDEX IF NOT EXISTS idx_logs_user ON system_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_logs_created ON system_logs(created_at);

-- ===== USER IMPORTS =====

CREATE TABLE IF NOT EXISTS user_imports (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  imported_by UUID NOT NULL REFERENCES users(id),
  filename VARCHAR(255) NOT NULL,
  total_rows INT NOT NULL,
  success_count INT DEFAULT 0,
  error_count INT DEFAULT 0,
  status VARCHAR(50) NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
  error_details JSONB,
  created_at TIMESTAMP DEFAULT NOW(),
  completed_at TIMESTAMP
);

-- ===== DEFAULT SETTINGS (only essentials, app config is in config.json) =====

INSERT INTO settings (setting_key, setting_value, description) VALUES
  ('visitor_redirect_url', '/visitor/events', 'Default redirect URL after login for visitors')
ON CONFLICT (setting_key) DO NOTHING;

-- Seed events
INSERT INTO events (id, organizer_id, name, description, venue, event_date, sale_start, sale_end, total_capacity, remaining_capacity, status, war_mode) VALUES
  ('e0000000-0000-0000-0000-000000000001', 'a0000000-0000-0000-0000-000000000002',
   'Rock Festival 2025', 'The biggest rock concert of the year!', 'Jakarta International Stadium',
   '2025-12-15 19:00:00', '2025-01-01 00:00:00', '2025-12-14 23:59:59', 10000, 10000, 'published', TRUE),
  ('e0000000-0000-0000-0000-000000000002', 'a0000000-0000-0000-0000-000000000002',
   'Jazz Night Special', 'An intimate evening of smooth jazz.', 'Balai Sarbini',
   '2025-11-20 20:00:00', '2025-01-01 00:00:00', '2025-11-19 23:59:59', 2000, 2000, 'published', FALSE)
ON CONFLICT (id) DO NOTHING;

-- Seed ticket classes
INSERT INTO ticket_classes (id, event_id, name, description, price, total_capacity, remaining_capacity, sale_window_start, sale_window_end) VALUES
  ('a0000000-0000-0000-0000-000000000001', 'e0000000-0000-0000-0000-000000000001', 'VVIP', 'Front row access & meet & greet', 5000000.00, 200, 200, '2025-01-01 00:00:00', '2025-12-14 23:59:59'),
  ('a0000000-0000-0000-0000-000000000002', 'e0000000-0000-0000-0000-000000000001', 'VIP', 'Priority seating area', 2500000.00, 800, 800, '2025-01-01 00:00:00', '2025-12-14 23:59:59'),
  ('a0000000-0000-0000-0000-000000000003', 'e0000000-0000-0000-0000-000000000001', 'Festival', 'General admission', 750000.00, 5000, 5000, '2025-01-01 00:00:00', '2025-12-14 23:59:59'),
  ('a0000000-0000-0000-0000-000000000004', 'e0000000-0000-0000-0000-000000000001', 'Regular', 'Back area access', 350000.00, 4000, 4000, '2025-01-01 00:00:00', '2025-12-14 23:59:59'),
  ('a0000000-0000-0000-0000-000000000005', 'e0000000-0000-0000-0000-000000000002', 'VIP Table', 'Premium table for 4', 3500000.00, 50, 50, '2025-01-01 00:00:00', '2025-11-19 23:59:59'),
  ('a0000000-0000-0000-0000-000000000006', 'e0000000-0000-0000-0000-000000000002', 'Premium Seat', 'Best view seats', 1500000.00, 500, 500, '2025-01-01 00:00:00', '2025-11-19 23:59:59'),
  ('a0000000-0000-0000-0000-000000000007', 'e0000000-0000-0000-0000-000000000002', 'Regular', 'Standard seating', 500000.00, 1450, 1450, '2025-01-01 00:00:00', '2025-11-19 23:59:59')
ON CONFLICT (id) DO NOTHING;

COMMIT;
