-- TripTale database schema v0.1
-- The installer replaces {{PREFIX}} with the configured table prefix.

CREATE TABLE {{PREFIX}}users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NULL,
    password_hash VARCHAR(255) NULL,
    profile_image VARCHAR(255) NULL,
    chat_color VARCHAR(20) NULL,
    role ENUM('main_admin','admin','participant') NOT NULL DEFAULT 'participant',
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_role (role),
    KEY idx_users_created_by (created_by),
    CONSTRAINT fk_users_created_by FOREIGN KEY (created_by)
        REFERENCES {{PREFIX}}users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}login_tokens (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    request_ip VARCHAR(45) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_login_tokens_hash (token_hash),
    KEY idx_login_tokens_user (user_id),
    KEY idx_login_tokens_expiry (expires_at),
    CONSTRAINT fk_login_tokens_user FOREIGN KEY (user_id)
        REFERENCES {{PREFIX}}users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}trips (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    subtitle VARCHAR(255) NULL,
    description TEXT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    cover_image VARCHAR(255) NULL,
    logo_image VARCHAR(255) NULL,
    status ENUM('draft','active','completed','archived') NOT NULL DEFAULT 'draft',
    auto_delete_days INT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_trips_created_by (created_by),
    KEY idx_trips_status (status),
    CONSTRAINT fk_trips_created_by FOREIGN KEY (created_by)
        REFERENCES {{PREFIX}}users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}trip_admins (
    trip_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (trip_id, user_id),
    CONSTRAINT fk_trip_admins_trip FOREIGN KEY (trip_id)
        REFERENCES {{PREFIX}}trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_admins_user FOREIGN KEY (user_id)
        REFERENCES {{PREFIX}}users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}trip_participants (
    trip_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (trip_id, user_id),
    CONSTRAINT fk_trip_participants_trip FOREIGN KEY (trip_id)
        REFERENCES {{PREFIX}}trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_trip_participants_user FOREIGN KEY (user_id)
        REFERENCES {{PREFIX}}users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}trip_days (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    trip_id BIGINT UNSIGNED NOT NULL,
    day_number INT UNSIGNED NOT NULL,
    date DATE NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_trip_days_number (trip_id, day_number),
    KEY idx_trip_days_trip (trip_id),
    CONSTRAINT fk_trip_days_trip FOREIGN KEY (trip_id)
        REFERENCES {{PREFIX}}trips(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}attractions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    short_description TEXT NULL,
    description LONGTEXT NULL,
    address VARCHAR(500) NULL,
    city VARCHAR(150) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NOT NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_attractions_name (name),
    KEY idx_attractions_city (city),
    KEY idx_attractions_status (status),
    CONSTRAINT fk_attractions_created_by FOREIGN KEY (created_by)
        REFERENCES {{PREFIX}}users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_attractions_updated_by FOREIGN KEY (updated_by)
        REFERENCES {{PREFIX}}users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}attraction_images (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    attraction_id BIGINT UNSIGNED NOT NULL,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_attraction_images_attraction (attraction_id),
    CONSTRAINT fk_attraction_images_attraction FOREIGN KEY (attraction_id)
        REFERENCES {{PREFIX}}attractions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}accommodations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    address VARCHAR(500) NULL,
    city VARCHAR(150) NULL,
    website VARCHAR(500) NULL,
    phone VARCHAR(50) NULL,
    note TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NOT NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_accommodations_name (name),
    KEY idx_accommodations_city (city),
    CONSTRAINT fk_accommodations_created_by FOREIGN KEY (created_by)
        REFERENCES {{PREFIX}}users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_accommodations_updated_by FOREIGN KEY (updated_by)
        REFERENCES {{PREFIX}}users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}accommodation_images (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    accommodation_id BIGINT UNSIGNED NOT NULL,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_accommodation_images_accommodation (accommodation_id),
    CONSTRAINT fk_accommodation_images_accommodation FOREIGN KEY (accommodation_id)
        REFERENCES {{PREFIX}}accommodations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}restaurants (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    description LONGTEXT NULL,
    address VARCHAR(500) NULL,
    city VARCHAR(150) NULL,
    website VARCHAR(500) NULL,
    phone VARCHAR(50) NULL,
    note TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NOT NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_restaurants_name (name),
    KEY idx_restaurants_city (city),
    CONSTRAINT fk_restaurants_created_by FOREIGN KEY (created_by)
        REFERENCES {{PREFIX}}users(id) ON DELETE RESTRICT,
    CONSTRAINT fk_restaurants_updated_by FOREIGN KEY (updated_by)
        REFERENCES {{PREFIX}}users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}restaurant_images (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    restaurant_id BIGINT UNSIGNED NOT NULL,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_restaurant_images_restaurant (restaurant_id),
    CONSTRAINT fk_restaurant_images_restaurant FOREIGN KEY (restaurant_id)
        REFERENCES {{PREFIX}}restaurants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}categories (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(180) NOT NULL,
    type ENUM('attraction','accommodation','restaurant') NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_categories_slug_type (slug, type),
    KEY idx_categories_type_status (type, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}attraction_categories (
    attraction_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (attraction_id, category_id),
    CONSTRAINT fk_attraction_categories_attraction FOREIGN KEY (attraction_id)
        REFERENCES {{PREFIX}}attractions(id) ON DELETE CASCADE,
    CONSTRAINT fk_attraction_categories_category FOREIGN KEY (category_id)
        REFERENCES {{PREFIX}}categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}accommodation_categories (
    accommodation_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (accommodation_id, category_id),
    CONSTRAINT fk_accommodation_categories_accommodation FOREIGN KEY (accommodation_id)
        REFERENCES {{PREFIX}}accommodations(id) ON DELETE CASCADE,
    CONSTRAINT fk_accommodation_categories_category FOREIGN KEY (category_id)
        REFERENCES {{PREFIX}}categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}restaurant_categories (
    restaurant_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (restaurant_id, category_id),
    CONSTRAINT fk_restaurant_categories_restaurant FOREIGN KEY (restaurant_id)
        REFERENCES {{PREFIX}}restaurants(id) ON DELETE CASCADE,
    CONSTRAINT fk_restaurant_categories_category FOREIGN KEY (category_id)
        REFERENCES {{PREFIX}}categories(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}trip_day_items (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    trip_day_id BIGINT UNSIGNED NOT NULL,
    item_type ENUM('attraction','accommodation','restaurant','custom') NOT NULL,
    reference_id BIGINT UNSIGNED NULL,
    title VARCHAR(255) NULL,
    time TIME NULL,
    trip_additional_info LONGTEXT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_trip_day_items_day_order (trip_day_id, sort_order),
    KEY idx_trip_day_items_reference (item_type, reference_id),
    CONSTRAINT fk_trip_day_items_day FOREIGN KEY (trip_day_id)
        REFERENCES {{PREFIX}}trip_days(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}notes (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    trip_id BIGINT UNSIGNED NOT NULL,
    trip_day_id BIGINT UNSIGNED NULL,
    trip_day_item_id BIGINT UNSIGNED NULL,
    title VARCHAR(255) NOT NULL,
    content LONGTEXT NOT NULL,
    visibility ENUM('private','group') NOT NULL DEFAULT 'private',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_notes_user_trip (user_id, trip_id),
    KEY idx_notes_trip (trip_id),
    KEY idx_notes_day (trip_day_id),
    KEY idx_notes_item (trip_day_item_id),
    CONSTRAINT fk_notes_user FOREIGN KEY (user_id)
        REFERENCES {{PREFIX}}users(id) ON DELETE CASCADE,
    CONSTRAINT fk_notes_trip FOREIGN KEY (trip_id)
        REFERENCES {{PREFIX}}trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_notes_day FOREIGN KEY (trip_day_id)
        REFERENCES {{PREFIX}}trip_days(id) ON DELETE CASCADE,
    CONSTRAINT fk_notes_item FOREIGN KEY (trip_day_item_id)
        REFERENCES {{PREFIX}}trip_day_items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}chat_messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    trip_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    message TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    is_anonymous TINYINT(1) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_chat_trip_created (trip_id, created_at),
    KEY idx_chat_user (user_id),
    CONSTRAINT fk_chat_trip FOREIGN KEY (trip_id)
        REFERENCES {{PREFIX}}trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_chat_user FOREIGN KEY (user_id)
        REFERENCES {{PREFIX}}users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}pdf_exports (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    trip_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_pdf_exports_trip_user (trip_id, user_id),
    CONSTRAINT fk_pdf_exports_trip FOREIGN KEY (trip_id)
        REFERENCES {{PREFIX}}trips(id) ON DELETE CASCADE,
    CONSTRAINT fk_pdf_exports_user FOREIGN KEY (user_id)
        REFERENCES {{PREFIX}}users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}audit_log (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) NULL,
    entity_id BIGINT UNSIGNED NULL,
    metadata JSON NULL,
    ip_address VARCHAR(45) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_audit_user_created (user_id, created_at),
    KEY idx_audit_entity (entity_type, entity_id),
    CONSTRAINT fk_audit_user FOREIGN KEY (user_id)
        REFERENCES {{PREFIX}}users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE {{PREFIX}}settings (
    setting_key VARCHAR(100) NOT NULL,
    setting_value LONGTEXT NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
