CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','user') DEFAULT 'user',
    status ENUM('active','suspended','expired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    price DECIMAL(15,2) DEFAULT 0,
    duration_days INT DEFAULT 30,

    max_invitations INT DEFAULT 1,
    max_guests INT DEFAULT 100,
    max_gallery INT DEFAULT 20,

    feature_rsvp TINYINT(1) DEFAULT 0,
    feature_guestbook TINYINT(1) DEFAULT 0,
    feature_gift TINYINT(1) DEFAULT 0,
    feature_music TINYINT(1) DEFAULT 0,
    feature_custom_domain TINYINT(1) DEFAULT 0,

    status TINYINT(1) DEFAULT 1,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE memberships (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,
    plan_id INT NOT NULL,

    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,

    status ENUM(
        'active',
        'expired',
        'cancelled'
    ) DEFAULT 'active',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    FOREIGN KEY (plan_id)
        REFERENCES plans(id)
        ON DELETE CASCADE
);

CREATE TABLE templates (
    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,
    thumbnail VARCHAR(255),

    category VARCHAR(100),

    premium TINYINT(1) DEFAULT 0,

    status TINYINT(1) DEFAULT 1,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE invitations (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,
    template_id INT NULL,

    slug VARCHAR(150) UNIQUE NOT NULL,

    groom_name VARCHAR(100),
    bride_name VARCHAR(100),

    groom_parent TEXT,
    bride_parent TEXT,

    quote TEXT,

    cover_image VARCHAR(255),

    music VARCHAR(255),

    status ENUM(
        'draft',
        'published',
        'expired'
    ) DEFAULT 'draft',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    FOREIGN KEY (template_id)
        REFERENCES templates(id)
        ON DELETE SET NULL
);

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,

    invitation_id INT NOT NULL,

    event_name VARCHAR(150),
    event_date DATE,
    start_time TIME,
    end_time TIME,

    venue VARCHAR(255),
    address TEXT,

    latitude VARCHAR(100),
    longitude VARCHAR(100),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (invitation_id)
        REFERENCES invitations(id)
        ON DELETE CASCADE
);

CREATE TABLE guests (
    id INT AUTO_INCREMENT PRIMARY KEY,

    invitation_id INT NOT NULL,

    name VARCHAR(150) NOT NULL,
    phone VARCHAR(50),

    attendance ENUM(
        'pending',
        'yes',
        'no'
    ) DEFAULT 'pending',

    message TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (invitation_id)
        REFERENCES invitations(id)
        ON DELETE CASCADE
);

CREATE TABLE gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,

    invitation_id INT NOT NULL,

    image VARCHAR(255) NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (invitation_id)
        REFERENCES invitations(id)
        ON DELETE CASCADE
);

CREATE TABLE gifts (
    id INT AUTO_INCREMENT PRIMARY KEY,

    invitation_id INT NOT NULL,

    bank_name VARCHAR(100),
    account_name VARCHAR(150),
    account_number VARCHAR(100),

    qris_image VARCHAR(255),

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (invitation_id)
        REFERENCES invitations(id)
        ON DELETE CASCADE
);

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,

    user_id INT NOT NULL,
    plan_id INT NOT NULL,

    amount DECIMAL(15,2),

    proof VARCHAR(255),

    status ENUM(
        'pending',
        'approved',
        'rejected'
    ) DEFAULT 'pending',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE,

    FOREIGN KEY (plan_id)
        REFERENCES plans(id)
        ON DELETE CASCADE
);

CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,

    setting_key VARCHAR(100) UNIQUE,
    setting_value TEXT
);