CREATE DATABASE IF NOT EXISTS wedding_planner;
USE wedding_planner;

CREATE TABLE IF NOT EXISTS planners (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    bride_name VARCHAR(100),
    groom_name VARCHAR(100),
    wedding_date DATE,
    budget_total DECIMAL(15, 2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS allocations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    planner_id INT NOT NULL,
    category_name VARCHAR(100) NOT NULL,
    allocated_amount DECIMAL(15, 2) DEFAULT 0,
    expense_amount DECIMAL(15, 2) DEFAULT 0,
    FOREIGN KEY (planner_id) REFERENCES planners(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS guests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    planner_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    total_invites INT DEFAULT 1,
    kids_count INT DEFAULT 0,
    category ENUM('groom_family', 'bride_family', 'groom_friend', 'bride_friend', 'other') DEFAULT 'other',
    rsvp_status ENUM('pending', 'attending', 'declined') DEFAULT 'pending',
    FOREIGN KEY (planner_id) REFERENCES planners(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    planner_id INT NOT NULL,
    vendor_name VARCHAR(100) NOT NULL,
    vendor_type VARCHAR(50),
    price DECIMAL(15, 2),
    status ENUM('proposed', 'booked', 'paid') DEFAULT 'proposed',
    FOREIGN KEY (planner_id) REFERENCES planners(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rundowns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    planner_id INT NOT NULL,
    time_start TIME,
    time_end TIME,
    activity VARCHAR(255),
    description TEXT,
    FOREIGN KEY (planner_id) REFERENCES planners(id) ON DELETE CASCADE
);
