-- ============================================================
-- Jetron Ticket - Full Database Schema
-- ============================================================
-- Run this once to set up your database.
-- mysql -u root -p < schema.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS jetron_ticket CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE jetron_ticket;

-- Drop in correct order (children first)
DROP TABLE IF EXISTS scan_logs;
DROP TABLE IF EXISTS tickets;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS ticket_types;
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS payouts;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS locations;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS admins;

-- Admins (backend users)
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(180) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('superadmin','admin','scanner') NOT NULL DEFAULT 'admin',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Customers / buyers
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(180) NOT NULL,
    phone VARCHAR(40) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);

-- Categories
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    icon VARCHAR(60) DEFAULT NULL,
    sort_order INT DEFAULT 0
);

-- Locations
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    country VARCHAR(60) DEFAULT 'Nigeria'
);

-- Events
CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(220) NOT NULL UNIQUE,
    description TEXT,
    cover_image VARCHAR(255) DEFAULT NULL,
    venue VARCHAR(200) DEFAULT NULL,
    address VARCHAR(255) DEFAULT NULL,
    location_id INT DEFAULT NULL,
    category_id INT DEFAULT NULL,
    starts_at DATETIME NOT NULL,
    ends_at DATETIME DEFAULT NULL,
    host_name VARCHAR(150) DEFAULT NULL,
    status ENUM('draft','published','cancelled','sold_out') NOT NULL DEFAULT 'draft',
    is_featured TINYINT(1) NOT NULL DEFAULT 0,
    is_trending TINYINT(1) NOT NULL DEFAULT 0,
    is_sponsored TINYINT(1) NOT NULL DEFAULT 0,
    created_by INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_starts_at (starts_at),
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES admins(id) ON DELETE SET NULL
);

-- Ticket types per event (Regular, VIP, Table for 5, etc.)
CREATE TABLE ticket_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    price DECIMAL(12,2) NOT NULL DEFAULT 0,
    quantity INT NOT NULL DEFAULT 0,
    quantity_sold INT NOT NULL DEFAULT 0,
    sort_order INT DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

-- Orders
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_ref VARCHAR(40) NOT NULL UNIQUE,
    user_id INT DEFAULT NULL,
    buyer_name VARCHAR(120) NOT NULL,
    buyer_email VARCHAR(180) NOT NULL,
    buyer_phone VARCHAR(40) DEFAULT NULL,
    event_id INT NOT NULL,
    subtotal DECIMAL(12,2) NOT NULL DEFAULT 0,
    fees DECIMAL(12,2) NOT NULL DEFAULT 0,
    total DECIMAL(12,2) NOT NULL DEFAULT 0,
    currency VARCHAR(8) NOT NULL DEFAULT 'NGN',
    payment_status ENUM('pending','paid','failed','refunded') NOT NULL DEFAULT 'pending',
    payment_method VARCHAR(40) DEFAULT NULL,
    payment_ref VARCHAR(100) DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    INDEX idx_status (payment_status),
    INDEX idx_created (created_at)
);

-- Order line items (qty per ticket type)
CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    ticket_type_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(12,2) NOT NULL,
    line_total DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (ticket_type_id) REFERENCES ticket_types(id) ON DELETE CASCADE
);

-- Individual tickets (one row per ticket issued, each has unique code)
CREATE TABLE tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    ticket_type_id INT NOT NULL,
    event_id INT NOT NULL,
    code VARCHAR(64) NOT NULL UNIQUE,
    holder_name VARCHAR(120) DEFAULT NULL,
    status ENUM('valid','used','cancelled') NOT NULL DEFAULT 'valid',
    used_at DATETIME DEFAULT NULL,
    used_by_admin_id INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (ticket_type_id) REFERENCES ticket_types(id) ON DELETE CASCADE,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    FOREIGN KEY (used_by_admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    INDEX idx_code (code),
    INDEX idx_status (status)
);

-- Scan log (audit trail of every scan attempt)
CREATE TABLE scan_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_code VARCHAR(64) NOT NULL,
    ticket_id INT DEFAULT NULL,
    admin_id INT DEFAULT NULL,
    result ENUM('valid','already_used','invalid','wrong_event','cancelled') NOT NULL,
    notes VARCHAR(255) DEFAULT NULL,
    scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE SET NULL,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL
);

-- Payouts to event hosts/organizers
CREATE TABLE payouts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    currency VARCHAR(8) NOT NULL DEFAULT 'NGN',
    method VARCHAR(40) DEFAULT 'bank_transfer',
    reference VARCHAR(100) DEFAULT NULL,
    status ENUM('pending','processing','completed','failed') NOT NULL DEFAULT 'pending',
    notes TEXT,
    requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME DEFAULT NULL,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

-- ============================================================
-- Seed data
-- ============================================================

-- Default admin: email=admin@jetron.test  password=admin123
INSERT INTO admins (name, email, password_hash, role) VALUES
('Super Admin', 'admin@jetron.test', '$2y$10$Z3wPuEqYE.G3yUdj7NcKzuELfYPpE1A8GfxBVbsbDJbVvnf.eC8Wq', 'superadmin');
-- Note: the hash above is bcrypt of "admin123". You can also reset using install.php

INSERT INTO categories (name, slug, icon, sort_order) VALUES
('Nightlife and Clubbing', 'nightlife-and-clubbing', '🎉', 1),
('Concerts and Live Performances', 'concerts-and-live-performances', '🎤', 2),
('Outdoor Parties and Festivals', 'outdoor-parties-and-festivals', '🌅', 3),
('Themed and House Parties', 'themed-and-house-parties', '🏠', 4),
('Raves and EDM', 'raves-and-edm', '🎧', 5),
('Food and Drink', 'food-and-drink', '🍽️', 6),
('Sports and Fitness', 'sports-and-fitness', '⚽', 7),
('Art and Culture', 'art-and-culture', '🎨', 8),
('Fashion and Lifestyle', 'fashion-and-lifestyle', '👗', 9),
('Meetups and Socials', 'meetups-and-socials', '🤝', 10),
('Workshops and Conventions', 'workshops-and-conventions', '🎓', 11),
('Others', 'others', '✨', 12);

INSERT INTO locations (name, slug, country) VALUES
('Lagos', 'lagos', 'Nigeria'),
('Abuja', 'abuja', 'Nigeria'),
('Oyo', 'oyo', 'Nigeria'),
('Ghana', 'ghana', 'Ghana'),
('South Africa', 'south-africa', 'South Africa');

-- Sample events
INSERT INTO events (title, slug, description, venue, address, location_id, category_id, starts_at, ends_at, host_name, status, is_featured, is_trending, is_sponsored) VALUES
('Detty December: Lagos Edition', 'detty-december-lagos', 'The biggest end-of-year party in Lagos. Live DJs, open bar, surprise performances.', 'Eko Hotel & Suites', 'Plot 1415 Adetokunbo Ademola Street, Victoria Island', 1, 1, '2026-06-15 20:00:00', '2026-06-16 04:00:00', 'Detty Crew', 'published', 1, 1, 1),
('Loud House: Afro Edition', 'loud-house-afro', 'A night of pure afrobeats energy. Headlined by top Nigerian DJs.', 'Hard Rock Cafe', 'Water Corporation Drive, Oniru', 1, 5, '2026-06-22 21:00:00', '2026-06-23 03:00:00', 'Loud House NG', 'published', 0, 1, 0),
('Beat the Heat Pool Party', 'beat-the-heat', 'Daytime pool party with cocktails, sun, and great vibes.', 'La Campagne Tropicana', 'Lekki Free Trade Zone', 1, 3, '2026-07-05 13:00:00', '2026-07-05 21:00:00', 'Tropicana Events', 'published', 1, 0, 1),
('Abuja Football Hangout', 'abuja-football-hangout', 'Watch the big match with fellow fans. Drinks, snacks, big screens.', 'The Yard, Maitama', 'Maitama, Abuja', 2, 7, '2026-06-18 18:00:00', '2026-06-18 23:00:00', 'AFH', 'published', 0, 0, 0),
('Paint N Chill', 'paint-n-chill', 'A relaxed evening of guided painting, wine, and live music.', 'The Art Cafe', 'Lekki Phase 1', 1, 8, '2026-06-29 17:00:00', '2026-06-29 21:00:00', 'Art Cafe', 'published', 0, 0, 0),
('Y2K Party', 'y2k-party', 'Throwback to the 2000s. Outfits encouraged. Best dressed wins prizes.', 'Quilox', 'Ozumba Mbadiwe, Victoria Island', 1, 4, '2026-07-12 22:00:00', '2026-07-13 04:00:00', 'Throwback NG', 'published', 0, 1, 0);

-- Ticket types for those events
INSERT INTO ticket_types (event_id, name, description, price, quantity, quantity_sold, sort_order) VALUES
(1, 'Regular', 'General admission', 5000.00, 500, 120, 1),
(1, 'VIP', 'VIP section + 1 free drink', 15000.00, 100, 30, 2),
(1, 'Table for 5', 'Reserved table, bottle of premium spirit', 80000.00, 20, 4, 3),
(2, 'Early Bird', 'Limited early bird tickets', 3500.00, 200, 200, 1),
(2, 'Regular', 'General admission', 6000.00, 300, 80, 2),
(3, 'Standard', 'Pool access + welcome drink', 8000.00, 250, 60, 1),
(3, 'Cabana', 'Private cabana for 4 people', 50000.00, 15, 3, 2),
(4, 'Entry', 'Entry + 1 drink', 2500.00, 150, 22, 1),
(5, 'Standard', 'Includes painting kit + 1 glass of wine', 12000.00, 40, 11, 1),
(6, 'Regular', 'General admission', 5000.00, 400, 90, 1),
(6, 'VIP', 'VIP lounge access', 12000.00, 80, 18, 2);