-- BookBeach Database Schema
-- PostgreSQL version

-- Create database (run this separately if needed)
-- CREATE DATABASE bookbeach;

-- Use the database
--\c bookbeach;

-- Create extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Countries table
CREATE TABLE countries (
    country_id SERIAL PRIMARY KEY,
    country_name VARCHAR(100) NOT NULL,
    country_code VARCHAR(3) NOT NULL UNIQUE,
    phone_code VARCHAR(10),
    flag_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Currencies table
CREATE TABLE currencies (
    currency_id SERIAL PRIMARY KEY,
    currency_code VARCHAR(3) NOT NULL UNIQUE,
    currency_name VARCHAR(50) NOT NULL,
    currency_symbol VARCHAR(10),
    exchange_rate_to_eur DECIMAL(10, 6) DEFAULT 1.0,
    is_active BOOLEAN DEFAULT true,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Languages table
CREATE TABLE languages (
    language_id SERIAL PRIMARY KEY,
    language_code VARCHAR(5) NOT NULL UNIQUE,
    language_name VARCHAR(50) NOT NULL,
    flag_url VARCHAR(500),
    is_active BOOLEAN DEFAULT true
);

-- User roles table
CREATE TABLE user_roles (
    role_id SERIAL PRIMARY KEY,
    role_name VARCHAR(30) NOT NULL UNIQUE,
    description TEXT
);

-- Users table
CREATE TABLE users (
    user_id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(50),
    date_of_birth DATE,
    profile_photo_path VARCHAR(1000),
    role_id INTEGER REFERENCES user_roles(role_id),
    preferred_language_id INTEGER REFERENCES languages(language_id),
    preferred_currency_id INTEGER REFERENCES currencies(currency_id),
    is_email_verified BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

-- Email verification tokens
CREATE TABLE email_verification_tokens (
    token_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id),
    token VARCHAR(6) NOT NULL,
    token_type VARCHAR(30) NOT NULL, -- 'email_verification', 'password_reset'
    expires_at TIMESTAMP NOT NULL,
    is_used BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Companies table
CREATE TABLE companies (
    company_id BIGSERIAL PRIMARY KEY,
    company_name VARCHAR(150) NOT NULL,
    address VARCHAR(250) NOT NULL,
    country_id INTEGER REFERENCES countries(country_id),
    province VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL,
    postal_code VARCHAR(10) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    mobile VARCHAR(50),
    fax VARCHAR(50),
    email VARCHAR(50) NOT NULL,
    registration_number VARCHAR(50),
    vat_number VARCHAR(50),
    tax_office VARCHAR(50),
    website VARCHAR(50),
    create_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    update_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    remarks TEXT,
    company_logo_path VARCHAR(1000),
    company_status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'approved', 'rejected', 'suspended'
    send_time_reports TIME DEFAULT '09:00:00'
);

-- User-Company relationships
CREATE TABLE user_companies (
    user_company_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id),
    company_id BIGINT REFERENCES companies(company_id),
    role VARCHAR(30) DEFAULT 'owner', -- 'owner', 'manager', 'employee'
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, company_id)
);

-- Beach terrain types table
CREATE TABLE beach_terrain_types (
    terrain_type_id SERIAL PRIMARY KEY,
    terrain_name VARCHAR(50) NOT NULL,
    photo_path VARCHAR(500),
    description TEXT
);

-- Beach bed types table
CREATE TABLE beach_bed_types (
    bed_type_id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT NOT NULL,
    photo_path VARCHAR(1000) NOT NULL,
    uid SMALLINT NOT NULL,
    is_layout BOOLEAN NOT NULL DEFAULT false,
    has_background_color BOOLEAN NOT NULL DEFAULT false
);

-- Beach places table
CREATE TABLE beach_places (
    beach_place_id BIGSERIAL PRIMARY KEY,
    beach_name VARCHAR(50) NOT NULL,
    company_id BIGINT REFERENCES companies(company_id),
    mask TEXT NOT NULL,
    max_seats SMALLINT NOT NULL,
    top_rows SMALLINT NOT NULL,
    top_seats SMALLINT NOT NULL,
    is_double_seat BOOLEAN NOT NULL DEFAULT false,
    has_umbrella BOOLEAN NOT NULL DEFAULT false,
    has_showers BOOLEAN NOT NULL DEFAULT false,
    has_lockers BOOLEAN NOT NULL DEFAULT false,
    has_market BOOLEAN NOT NULL DEFAULT false,
    has_restaurant BOOLEAN NOT NULL DEFAULT false,
    has_swimming_pool BOOLEAN NOT NULL DEFAULT false,
    has_parking BOOLEAN NOT NULL DEFAULT false,
    has_toilets BOOLEAN NOT NULL DEFAULT false,
    has_free_wifi BOOLEAN NOT NULL DEFAULT false,
    has_music BOOLEAN NOT NULL DEFAULT false,
    has_safety_box BOOLEAN NOT NULL DEFAULT false,
    has_access_wheelchair BOOLEAN NOT NULL DEFAULT false,
    has_lockers_for_baby BOOLEAN NOT NULL DEFAULT false,
    has_beach_volley BOOLEAN NOT NULL DEFAULT false,
    has_tennis BOOLEAN NOT NULL DEFAULT false,
    has_spa BOOLEAN NOT NULL DEFAULT false,
    has_medical BOOLEAN NOT NULL DEFAULT false,
    has_lifeguard BOOLEAN NOT NULL DEFAULT false,
    pet_allow BOOLEAN NOT NULL DEFAULT false,
    enable_beach BOOLEAN NOT NULL DEFAULT true,
    beach_information TEXT NOT NULL,
    address VARCHAR(250) NOT NULL,
    latitude DECIMAL(18, 8) NOT NULL,
    longitude DECIMAL(18, 8) NOT NULL,
    terrain_type_id INTEGER REFERENCES beach_terrain_types(terrain_type_id),
    country_id INTEGER REFERENCES countries(country_id),
    city VARCHAR(50) NOT NULL,
    disable_today BOOLEAN NOT NULL DEFAULT false,
    -- Additional fields for beach design
    has_sea BOOLEAN NOT NULL DEFAULT false,
    sea_location VARCHAR(10) DEFAULT '', -- 'T' for top, 'L' for left
    background_photo VARCHAR(1000) DEFAULT '',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Beach places photos table
CREATE TABLE beach_places_photos (
    photo_id BIGSERIAL PRIMARY KEY,
    beach_place_id BIGINT REFERENCES beach_places(beach_place_id),
    photo_primary BOOLEAN NOT NULL DEFAULT false,
    photo_path VARCHAR(1000) NOT NULL,
    caption VARCHAR(255),
    sort_order INTEGER DEFAULT 0
);

-- Beach places schedules table
CREATE TABLE beach_places_schedules (
    schedule_id BIGSERIAL PRIMARY KEY,
    beach_place_id BIGINT REFERENCES beach_places(beach_place_id),
    from_date DATE NOT NULL,
    to_date DATE NOT NULL,
    from_time TIME NOT NULL,
    to_time TIME NOT NULL,
    valid_dates VARCHAR(7) NOT NULL, -- '1234567' for Mon-Sun
    min_hours SMALLINT NOT NULL,
    can_refund BOOLEAN NOT NULL DEFAULT false,
    refund_before_hours SMALLINT NOT NULL DEFAULT 0,
    currency_id BIGINT REFERENCES currencies(currency_id),
    price DECIMAL(10, 2) NOT NULL,
    price_vip DECIMAL(10, 2) NOT NULL,
    extras TEXT,
    extras_vip TEXT
);

-- Beach place terrains (beach design elements)
CREATE TABLE beach_place_terrains (
    terrain_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    uid BIGINT NOT NULL,
    beach_place_id BIGINT REFERENCES beach_places(beach_place_id),
    angle SMALLINT NOT NULL DEFAULT 0,
    background_color VARCHAR(7) NOT NULL DEFAULT '#FFFFFF',
    bed_reference VARCHAR(30),
    block_online BOOLEAN NOT NULL DEFAULT false,
    classification SMALLINT NOT NULL DEFAULT 0,
    covers VARCHAR(30),
    description TEXT,
    description_text TEXT,
    description_text_position TEXT,
    height DECIMAL(10, 2) NOT NULL,
    left_position DECIMAL(18, 8) NOT NULL,
    bed_price DECIMAL(10, 2) NOT NULL DEFAULT 0,
    min_price DECIMAL(10, 2) NOT NULL DEFAULT 0,
    name VARCHAR(50) NOT NULL,
    top_position DECIMAL(18, 8) NOT NULL,
    width DECIMAL(10, 2) NOT NULL,
    z_index SMALLINT NOT NULL DEFAULT 0,
    is_layout BOOLEAN NOT NULL DEFAULT false
);

-- Restaurants table
CREATE TABLE restaurants (
    restaurant_id BIGSERIAL PRIMARY KEY,
    restaurant_name VARCHAR(100) NOT NULL,
    company_id BIGINT REFERENCES companies(company_id),
    beach_place_id BIGINT REFERENCES beach_places(beach_place_id),
    description TEXT,
    cuisine_type VARCHAR(50),
    opening_time TIME,
    closing_time TIME,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Restaurant menu categories
CREATE TABLE restaurant_categories (
    category_id BIGSERIAL PRIMARY KEY,
    restaurant_id BIGINT REFERENCES restaurants(restaurant_id),
    category_name VARCHAR(100) NOT NULL,
    description TEXT,
    sort_order INTEGER DEFAULT 0
);

-- Restaurant items table
CREATE TABLE restaurant_items (
    item_id BIGSERIAL PRIMARY KEY,
    restaurant_id BIGINT REFERENCES restaurants(restaurant_id),
    category_id BIGINT REFERENCES restaurant_categories(category_id),
    item_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    currency_id BIGINT REFERENCES currencies(currency_id),
    photo_path VARCHAR(1000),
    is_available BOOLEAN DEFAULT true,
    preparation_time INTEGER, -- in minutes
    allergens TEXT,
    is_vegetarian BOOLEAN DEFAULT false,
    is_vegan BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Markets table
CREATE TABLE markets (
    market_id BIGSERIAL PRIMARY KEY,
    market_name VARCHAR(100) NOT NULL,
    company_id BIGINT REFERENCES companies(company_id),
    beach_place_id BIGINT REFERENCES beach_places(beach_place_id),
    description TEXT,
    opening_time TIME,
    closing_time TIME,
    delivery_available BOOLEAN DEFAULT true,
    delivery_fee DECIMAL(10, 2) DEFAULT 0,
    min_order_amount DECIMAL(10, 2) DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Market categories
CREATE TABLE market_categories (
    category_id BIGSERIAL PRIMARY KEY,
    market_id BIGINT REFERENCES markets(market_id),
    category_name VARCHAR(100) NOT NULL,
    description TEXT,
    sort_order INTEGER DEFAULT 0
);

-- Market items table
CREATE TABLE market_items (
    item_id BIGSERIAL PRIMARY KEY,
    market_id BIGINT REFERENCES markets(market_id),
    category_id BIGINT REFERENCES market_categories(category_id),
    item_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    currency_id BIGINT REFERENCES currencies(currency_id),
    photo_path VARCHAR(1000),
    stock_quantity INTEGER DEFAULT 0,
    is_available BOOLEAN DEFAULT true,
    brand VARCHAR(50),
    barcode VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Adventures table
CREATE TABLE adventures (
    adventure_id BIGSERIAL PRIMARY KEY,
    adventure_name VARCHAR(100) NOT NULL,
    company_id BIGINT REFERENCES companies(company_id),
    beach_place_id BIGINT REFERENCES beach_places(beach_place_id),
    description TEXT,
    duration_minutes INTEGER,
    max_participants INTEGER,
    min_participants INTEGER DEFAULT 1,
    price DECIMAL(10, 2) NOT NULL,
    currency_id BIGINT REFERENCES currencies(currency_id),
    equipment_provided TEXT,
    requirements TEXT,
    safety_instructions TEXT,
    photo_path VARCHAR(1000),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Adventure schedules
CREATE TABLE adventure_schedules (
    schedule_id BIGSERIAL PRIMARY KEY,
    adventure_id BIGINT REFERENCES adventures(adventure_id),
    date DATE NOT NULL,
    start_time TIME NOT NULL,
    available_spots INTEGER NOT NULL,
    is_cancelled BOOLEAN DEFAULT false
);

-- Bookings table (for sunbeds)
CREATE TABLE bookings (
    booking_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id),
    beach_place_id BIGINT REFERENCES beach_places(beach_place_id),
    terrain_id UUID REFERENCES beach_place_terrains(terrain_id),
    booking_date DATE NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    currency_id BIGINT REFERENCES currencies(currency_id),
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'confirmed', 'cancelled', 'completed'
    payment_status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'paid', 'refunded'
    special_requests TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Reviews table
CREATE TABLE reviews (
    review_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id),
    beach_place_id BIGINT REFERENCES beach_places(beach_place_id),
    booking_id BIGINT REFERENCES bookings(booking_id),
    rating INTEGER CHECK (rating >= 1 AND rating <= 5),
    title VARCHAR(100),
    comment TEXT,
    photos TEXT[], -- Array of photo paths
    is_approved BOOLEAN DEFAULT false,
    admin_response TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, booking_id)
);

-- User wallet table
CREATE TABLE user_wallets (
    wallet_id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(user_id) UNIQUE,
    balance DECIMAL(12, 2) DEFAULT 0.00,
    currency_id BIGINT REFERENCES currencies(currency_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Wallet transactions table
CREATE TABLE wallet_transactions (
    transaction_id BIGSERIAL PRIMARY KEY,
    wallet_id BIGINT REFERENCES user_wallets(wallet_id),
    transaction_type VARCHAR(20) NOT NULL, -- 'deposit', 'withdrawal', 'payment', 'refund'
    amount DECIMAL(12, 2) NOT NULL,
    currency_id BIGINT REFERENCES currencies(currency_id),
    description TEXT,
    reference_id VARCHAR(100), -- External payment reference
    payment_method VARCHAR(30), -- 'credit_card', 'paypal', 'wechat', 'alipay', 'crypto', 'google_pay'
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'completed', 'failed', 'cancelled'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Email templates table
CREATE TABLE email_templates (
    template_id BIGSERIAL PRIMARY KEY,
    template_name VARCHAR(50) NOT NULL UNIQUE,
    subject VARCHAR(255) NOT NULL,
    html_content TEXT NOT NULL,
    text_content TEXT,
    variables JSON, -- Available template variables
    language_id INTEGER REFERENCES languages(language_id),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Email logs table
CREATE TABLE email_logs (
    log_id BIGSERIAL PRIMARY KEY,
    recipient_email VARCHAR(255) NOT NULL,
    template_id BIGINT REFERENCES email_templates(template_id),
    subject VARCHAR(255),
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'sent', 'failed'
    error_message TEXT,
    sent_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- System settings table
CREATE TABLE system_settings (
    setting_id BIGSERIAL PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);