# models.py
# Database layer for VacationWeb (MySQL / MariaDB)

import mysql.connector
from mysql.connector import Error


# =========================
# DATABASE CONFIG
# =========================

DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "",          # put your phpMyAdmin password if any
    "database": "vacationweb"
}


def get_db_connection():
    return mysql.connector.connect(**DB_CONFIG)


# =========================
# USER MODELS
# =========================

def get_user_by_email(email):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute(
        "SELECT * FROM users WHERE email = %s",
        (email,)
    )
    user = cursor.fetchone()

    cursor.close()
    conn.close()
    return user


def get_user_by_id(user_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute(
        "SELECT * FROM users WHERE user_id = %s",
        (user_id,)
    )
    user = cursor.fetchone()

    cursor.close()
    conn.close()
    return user


def create_user(name, email, password, phone_number):
    conn = get_db_connection()
    cursor = conn.cursor()

    try:
        cursor.execute(
            """
            INSERT INTO users (name, email, password, phone_number)
            VALUES (%s, %s, %s, %s)
            """,
            (name, email, password, phone_number)
        )
        conn.commit()
        return True
    except Error:
        return False
    finally:
        cursor.close()
        conn.close()


# =========================
# PACKAGE MODELS
# =========================

def get_all_packages():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute(
        "SELECT * FROM package_overview_view"
    )
    packages = cursor.fetchall()

    cursor.close()
    conn.close()
    return packages


def get_package_by_id(package_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute(
        """
        SELECT p.*, d.destination_name, s.state_name
        FROM packages p
        JOIN destinations d ON p.destination_id = d.destination_id
        JOIN states s ON d.state_id = s.state_id
        WHERE p.package_id = %s
        """,
        (package_id,)
    )
    package = cursor.fetchone()

    cursor.close()
    conn.close()
    return package


# =========================
# STATE & DESTINATION MODELS
# =========================

def get_all_states():
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute("SELECT * FROM states ORDER BY state_name")
    states = cursor.fetchall()

    cursor.close()
    conn.close()
    return states


def get_destinations_by_state(state_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute(
        "SELECT * FROM destinations WHERE state_id = %s",
        (state_id,)
    )
    destinations = cursor.fetchall()

    cursor.close()
    conn.close()
    return destinations


# =========================
# BOOKING MODELS
# =========================

def create_booking(user_id, package_id, travel_date):
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute(
        """
        INSERT INTO bookings (user_id, package_id, booking_date, travel_date)
        VALUES (%s, %s, CURDATE(), %s)
        """,
        (user_id, package_id, travel_date)
    )

    conn.commit()
    cursor.close()
    conn.close()


def get_user_bookings(user_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute(
        """
        SELECT * FROM booking_details_view
        WHERE booking_id IN (
            SELECT booking_id FROM bookings WHERE user_id = %s
        )
        """,
        (user_id,)
    )
    bookings = cursor.fetchall()

    cursor.close()
    conn.close()
    return bookings


# =========================
# REVIEW MODELS (OPTIONAL)
# =========================

def add_review(user_id, package_id, rating, comment):
    conn = get_db_connection()
    cursor = conn.cursor()

    cursor.execute(
        """
        INSERT INTO reviews (user_id, package_id, rating, comment, review_date)
        VALUES (%s, %s, %s, %s, CURDATE())
        """,
        (user_id, package_id, rating, comment)
    )

    conn.commit()
    cursor.close()
    conn.close()


def get_package_reviews(package_id):
    conn = get_db_connection()
    cursor = conn.cursor(dictionary=True)

    cursor.execute(
        """
        SELECT r.*, u.name
        FROM reviews r
        JOIN users u ON r.user_id = u.user_id
        WHERE r.package_id = %s
        """,
        (package_id,)
    )
    reviews = cursor.fetchall()

    cursor.close()
    conn.close()
    return reviews