<?php
/**
 * Database Setup Script
 * Creates a fresh database with all necessary tables and data
 */

$dbPath = __DIR__ . '/data/yolo_charters.db';

// Remove old database if exists
if (file_exists($dbPath)) {
    unlink($dbPath);
}

// Create new database
$db = new SQLite3($dbPath);

// Run all migrations
$migrations = [
    '001_initial_schema.sql',
    '002_add_friend_companies.sql', 
    '007_create_quote_requests_table.sql',
    '008_add_location_description_to_yachts.sql'
];

echo "Setting up database...\n\n";

// Create all tables manually since we don't have migration files
// This is based on the current schema

// Create yachts table
$db->exec("CREATE TABLE IF NOT EXISTS yachts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    slug TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL,
    model TEXT NOT NULL,
    booking_manager_id TEXT,
    base_port TEXT,
    price_from REAL,
    year_built INTEGER,
    year_refit INTEGER,
    length_ft INTEGER,
    cabins INTEGER,
    bathrooms INTEGER,
    draught_ft REAL,
    engine_hp INTEGER,
    water_capacity_l REAL,
    fuel_capacity_l REAL,
    beam_ft REAL,
    displacement_kg REAL,
    main_sail TEXT,
    genoa TEXT,
    description TEXT,
    equipment TEXT,
    main_image TEXT,
    gallery TEXT,
    layout_image TEXT,
    location_description TEXT,
    featured INTEGER DEFAULT 0,
    published INTEGER DEFAULT 1,
    sort_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)");

// Create users table
$db->exec("CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    email TEXT,
    first_name TEXT,
    last_name TEXT,
    phone TEXT,
    role TEXT DEFAULT 'guest',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)");

// Create settings table
$db->exec("CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    setting_key TEXT NOT NULL UNIQUE,
    setting_value TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)");

// Create friend_companies table
$db->exec("CREATE TABLE IF NOT EXISTS friend_companies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    company_name TEXT NOT NULL,
    company_id TEXT NOT NULL,
    active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)");

// Create quote_requests table
$db->exec("CREATE TABLE IF NOT EXISTS quote_requests (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    yacht_id TEXT NOT NULL,
    yacht_name TEXT NOT NULL,
    yacht_model TEXT,
    pickup_date TEXT,
    dropoff_date TEXT,
    flexible_dates INTEGER DEFAULT 0,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL,
    country_code TEXT DEFAULT '+30',
    phone TEXT NOT NULL,
    message TEXT,
    status TEXT DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)");

// Create posts table
$db->exec("CREATE TABLE IF NOT EXISTS posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    slug TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL,
    content TEXT,
    excerpt TEXT,
    featured_image TEXT,
    published INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)");

// Create contacts table
$db->exec("CREATE TABLE IF NOT EXISTS contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    phone TEXT,
    message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
)");

echo "✅ Tables created\n\n";

// Insert default admin user (password: admin123)
$adminPassword = password_hash('admin123', PASSWORD_DEFAULT);
$db->exec("INSERT INTO users (username, password, email, first_name, last_name, role) 
    VALUES ('admin', '$adminPassword', 'admin@yolocharters.com', 'Admin', 'User', 'admin')");

echo "✅ Admin user created (username: admin, password: admin123)\n\n";

// Insert API key
$apiKey = '1d40-2e6fd6f2efe772cf331d6b748aa2f2e5ada768f4a9ff1c176e457c26dfb05eb806b2d0eab200e3a1a6e49aebf9074d84347878823b4808faa1bd281b5db5f9fe';
$db->exec("INSERT INTO settings (setting_key, setting_value) VALUES ('booking_manager_api_key', '$apiKey')");

echo "✅ API key added\n\n";

// Insert friend companies
$db->exec("INSERT INTO friend_companies (company_name, company_id, active) VALUES 
    ('Apeiron Yachting', '4366', 1),
    ('Albatross Yachting', '3604', 1)");

echo "✅ Friend companies added\n\n";

echo "========================================\n";
echo "Database setup complete!\n";
echo "Location: $dbPath\n";
echo "========================================\n\n";

echo "Now run: php import-friends-boats.php\n";
echo "to import all friend's boats into the database.\n";

$db->close();