-- Database for Agadir Visite Tours
-- Note: Database 'agadnrgf_itsforyoutours' already exists in Namecheap hosting
-- Just run the table creation and insert statements below

USE agadnrgf_itsforyoutours;

-- Excursions Table
CREATE TABLE IF NOT EXISTS excursions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    duration VARCHAR(100) NOT NULL,
    distance VARCHAR(100),
    image VARCHAR(500),
    photos INT DEFAULT 0,
    reviews INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Circuits Table
CREATE TABLE IF NOT EXISTS circuits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    duration VARCHAR(100) NOT NULL,
    image VARCHAR(500),
    offers INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Activities Table
CREATE TABLE IF NOT EXISTS activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    duration VARCHAR(100) NOT NULL,
    image VARCHAR(500),
    photos INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Blog Posts Table
CREATE TABLE IF NOT EXISTS blog_posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    excerpt TEXT,
    content LONGTEXT,
    author VARCHAR(100) NOT NULL,
    language VARCHAR(10) DEFAULT 'fr',
    featured_image VARCHAR(500),
    day VARCHAR(2),
    month VARCHAR(10),
    year VARCHAR(4),
    status ENUM('draft', 'published') DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reviews Table
CREATE TABLE IF NOT EXISTS reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    date VARCHAR(50) NOT NULL,
    rating INT DEFAULT 5,
    title VARCHAR(255) NOT NULL,
    text TEXT NOT NULL,
    avatar VARCHAR(500),
    status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bookings Table
CREATE TABLE IF NOT EXISTS bookings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(100) UNIQUE NOT NULL,
    tour_type ENUM('excursion', 'circuit', 'activity') NOT NULL,
    tour_id INT NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    hotel VARCHAR(255),
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    booking_date DATE NOT NULL,
    booking_time TIME NOT NULL,
    number_of_people INT NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    supplements JSON,
    status ENUM('pending', 'confirmed', 'cancelled', 'completed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Invoices Table
CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(100) UNIQUE NOT NULL,
    invoice_date DATE NOT NULL,
    due_date DATE,
    client_name VARCHAR(255) NOT NULL,
    client_email VARCHAR(255),
    client_phone VARCHAR(50),
    client_address TEXT,
    client_city VARCHAR(100),
    client_postal_code VARCHAR(20),
    client_country VARCHAR(100) DEFAULT 'Morocco',
    subtotal DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    tax_rate DECIMAL(5, 2) DEFAULT 0.00,
    tax_amount DECIMAL(10, 2) DEFAULT 0.00,
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'MAD',
    payment_status ENUM('pending', 'paid', 'overdue', 'cancelled') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Invoice Items Table
CREATE TABLE IF NOT EXISTS invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    description TEXT NOT NULL,
    quantity DECIMAL(10, 2) NOT NULL DEFAULT 1.00,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Quotations Table
CREATE TABLE IF NOT EXISTS quotations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quotation_number VARCHAR(100) UNIQUE NOT NULL,
    quotation_date DATE NOT NULL,
    valid_until DATE,
    client_name VARCHAR(255) NOT NULL,
    client_email VARCHAR(255),
    client_phone VARCHAR(50),
    client_address TEXT,
    client_city VARCHAR(100),
    client_postal_code VARCHAR(20),
    client_country VARCHAR(100) DEFAULT 'Morocco',
    subtotal DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    tax_rate DECIMAL(5, 2) DEFAULT 0.00,
    tax_amount DECIMAL(10, 2) DEFAULT 0.00,
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    currency VARCHAR(10) DEFAULT 'MAD',
    status ENUM('pending', 'accepted', 'rejected', 'expired') DEFAULT 'pending',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Quotation Items Table
CREATE TABLE IF NOT EXISTS quotation_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    quotation_id INT NOT NULL,
    description TEXT NOT NULL,
    quantity DECIMAL(10, 2) NOT NULL DEFAULT 1.00,
    unit_price DECIMAL(10, 2) NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (quotation_id) REFERENCES quotations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Company Information Table
CREATE TABLE IF NOT EXISTS company_info (
    id INT PRIMARY KEY DEFAULT 1,
    company_name VARCHAR(255) NOT NULL,
    trade_name VARCHAR(255),
    tax_id VARCHAR(100),
    registration_number VARCHAR(100),
    address TEXT,
    city VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100) DEFAULT 'Morocco',
    phone VARCHAR(50),
    email VARCHAR(255),
    website VARCHAR(255),
    logo_url VARCHAR(500),
    bank_name VARCHAR(255),
    bank_account VARCHAR(255),
    bank_swift VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default company information
INSERT INTO company_info (
    company_name, trade_name, tax_id, registration_number, 
    address, city, postal_code, country, phone, email, website
) VALUES (
    'AGADIR VISITE', 'Agadir Visite Tours',
    '', '',
    'Agadir, Souss-Massa Province', 'Agadir', '', 'Morocco',
    '+212 6 61 75 20 25', 'info@agadir-visite.com', 'https://itsforyoutours.com'
) ON DUPLICATE KEY UPDATE 
    company_name = VALUES(company_name);

-- Insert sample data
INSERT INTO excursions (title, description, price, duration, distance, image, photos, reviews) VALUES
('Visite d''Agadir & Souk (½j)', 'Découvrez le symbole emblématique d''Agadir : la Kasbah perchée sur la colline.', 8.00, '5 heures', '30 Km', 'https://picsum.photos/400/300?image=1078', 3, 5),
('Excursion Marrakech (1j)', 'Plongez dans l''histoire millénaire de Marrakech, la perle du Sud.', 30.00, '1 journée', '235 Km', 'https://images.unsplash.com/photo-1516541196182-6bdb0516ed27?q=80&w=800', 7, 7);

INSERT INTO circuits (title, description, price, duration, image, offers) VALUES
('Circuit Taroudant / Marrakech (3j)', 'Un voyage unique à travers les montagnes de l''Atlas jusqu''à la perle du Sud.', 160.00, '3 journée', 'https://images.unsplash.com/photo-1517231428016-60f1985e2f73?q=80&w=800', 5),
('Circuit Grand Sud Marocain - Ouarzazat (3j)', 'Plongez dans le cinéma marocain à Ouarzazat, "la Hollywood du Sahara".', 170.00, '3 journée', 'https://images.unsplash.com/photo-1517623735006-137865a7e2d5?q=80&w=800', 4);

INSERT INTO activities (title, description, price, duration, image, photos) VALUES
('Balade à Dos de Dromadaire', 'Vivez une expérience authentique à dos de dromadaire le long des plages d''Agadir.', 15.00, '2 heures', 'https://images.unsplash.com/photo-1506926927443-40d0d6b4b9c1?q=80&w=800', 7),
('Balade à Cheval', 'Galopez le long des plages immaculées d''Agadir ou explorez les sentiers de l''arrière-pays.', 25.00, '2 heures', 'https://images.unsplash.com/photo-1544966503-7cc5ac882d5e?q=80&w=800', 2);

INSERT INTO blog_posts (title, excerpt, author, language, day, month) VALUES
('3 Days Circuit Taroudant – Marrakech: What to Expect + Best Things to Do', 'Go through the marvelous 3-day Atlassilex Tours from Agadir to Taroudant, over the Tizi n''Test...', 'admin', 'en', '13', 'Déc'),
('Les meilleures excursions d''Agadir', 'Découvrez les excursions incontournables de la région d''Agadir.', 'admin', 'fr', '20', 'Jan');

INSERT INTO reviews (name, date, rating, title, text, avatar, status) VALUES
('José Oliveira', '03/10/2022 18:00', 5, 'Excellent service', 'Bonjour Hamid, je tiens à te remercier pour l''excellent service rendu.', 'https://ui-avatars.com/api/?name=José+Oliveira&background=random', 'approved'),
('Sophie M', '15/09/2024', 5, 'Séjour inoubliable', 'Nous avons passés de merveilleux moments en famille! Hamid est un guide en or!', 'https://ui-avatars.com/api/?name=Sophie+M&background=random', 'approved');

