CREATE DATABASE IF NOT EXISTS event_system;
USE event_system;

CREATE TABLE agencies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    address TEXT,
    email VARCHAR(255),
    contact VARCHAR(50),
    logo VARCHAR(255)
);

CREATE TABLE bank_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT,
    bank_name VARCHAR(255),
    account_number VARCHAR(255),
    branch VARCHAR(255),
    payment_for TEXT
);

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    agency_id INT,
    name VARCHAR(255),
    days INT,
    logo VARCHAR(255)
);

CREATE TABLE event_dates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT,
    day_number INT,
    date DATE
);

CREATE TABLE ticket_tiers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT,
    category ENUM('Concert', 'Parking', 'Sponsorship', 'Booth'),
    tier_name VARCHAR(255),
    inventory INT,
    price DECIMAL(10,2),
    description TEXT
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT,
    buyer_name VARCHAR(255),
    email VARCHAR(255),
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    ticket_tier_id INT,
    quantity INT,
    price DECIMAL(10,2)
);

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    method ENUM('PayPal', 'GCash', 'BDO'),
    participants INT,
    proof_image VARCHAR(255),
    date_deposited DATE,
    bank_details TEXT
);