-- KeMetroFest MySQL Database Schema

USE kemetrof_db;

-- 1. Settings Table
CREATE TABLE IF NOT EXISTS settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    event_name VARCHAR(255),
    event_tagline TEXT,
    start_date DATE,
    end_date DATE,
    start_time TIME,
    end_time TIME,
    location_name VARCHAR(255),
    location_address TEXT,
    logo_image LONGTEXT, -- Base64
    hero_image LONGTEXT,
    about_image LONGTEXT,
    experience_image LONGTEXT,
    form_image LONGTEXT,
    slot_image LONGTEXT,
    whatsapp_number VARCHAR(20),
    email VARCHAR(255),
    bank_name VARCHAR(100),
    bank_account VARCHAR(50),
    bank_holder VARCHAR(100),
    about_badge VARCHAR(50),
    about_title VARCHAR(255),
    about_description TEXT,
    meta_title VARCHAR(255),
    meta_description TEXT,
    instagram_url TEXT,
    youtube_url TEXT,
    facebook_url TEXT,
    show_instagram BOOLEAN DEFAULT TRUE,
    show_youtube BOOLEAN DEFAULT TRUE,
    show_facebook BOOLEAN DEFAULT TRUE,
    registration_deadline DATE,
    organizer_name VARCHAR(255),
    wa_template_partner TEXT,
    wa_template_media TEXT,
    wa_template_tenant TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert initial empty settings if not exists
INSERT INTO settings (id, event_name) SELECT 1, 'KeMetroFest' WHERE NOT EXISTS (SELECT 1 FROM settings WHERE id = 1);

-- 2. Admin Table
CREATE TABLE IF NOT EXISTS admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default admin (password: admin123 - hashed version should be used in production)
-- For this demo/simple setup, we'll use a simple password check
INSERT INTO admins (username, password) 
SELECT 'admin', 'admin123' 
WHERE NOT EXISTS (SELECT 1 FROM admins WHERE username = 'admin');

-- 3. Slots Config Table
CREATE TABLE IF NOT EXISTS slots (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    total_slots INT NOT NULL DEFAULT 0,
    position TEXT,
    image TEXT,
    available BOOLEAN DEFAULT TRUE
);

-- 4. Partnership Tiers Table
CREATE TABLE IF NOT EXISTS partnership_tiers (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    icon VARCHAR(50),
    color VARCHAR(50),
    benefits TEXT,
    price VARCHAR(100)
);

-- 5. Experiences Table
CREATE TABLE IF NOT EXISTS experiences (
    id VARCHAR(50) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    series VARCHAR(100),
    description TEXT,
    image LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 6. Tenant Submissions Table
CREATE TABLE IF NOT EXISTS tenants (
    id VARCHAR(50) PRIMARY KEY,
    business_name VARCHAR(255) NOT NULL,
    contact_name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(255),
    category VARCHAR(100),
    block VARCHAR(50),
    assigned_slot VARCHAR(50),
    status VARCHAR(20) DEFAULT 'pending',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 7. Partners Data
CREATE TABLE IF NOT EXISTS partners (
    id VARCHAR(50) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(20),
    tier_id VARCHAR(50),
    contact_person VARCHAR(255),
    phone VARCHAR(20),
    email VARCHAR(255),
    logo LONGTEXT,
    status VARCHAR(20) DEFAULT 'active',
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
