-- Hotel Booking Migration for Tasks 14-40
-- Run this in phpMyAdmin > hotel-booking database > SQL tab

-- Add payment columns to bookings
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS price DECIMAL(10,2) DEFAULT 0.00;
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS payment_status VARCHAR(20) DEFAULT 'pending';
ALTER TABLE bookings ADD COLUMN IF NOT EXISTS transaction_ref VARCHAR(100) DEFAULT NULL;

-- Add price to rooms (Ghana Cedis - GHS)
ALTER TABLE rooms ADD COLUMN IF NOT EXISTS price DECIMAL(10,2) DEFAULT 0.00;

-- Create admins table
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    mypassword VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- SET ROOM PRICES (Ghana Cedis / GHS)
-- ============================================================
-- Update ALL existing rooms with tiered pricing based on their ID
-- Adjust these prices to match your actual room names/types

UPDATE rooms SET price = 850.00  WHERE id = 1;   -- Standard Room
UPDATE rooms SET price = 1200.00 WHERE id = 2;   -- Deluxe Room
UPDATE rooms SET price = 1800.00 WHERE id = 3;   -- Suite
UPDATE rooms SET price = 950.00  WHERE id = 4;   -- Executive Room
UPDATE rooms SET price = 2500.00 WHERE id = 5;   -- Presidential Suite
UPDATE rooms SET price = 750.00  WHERE id = 6;   -- Economy Room
UPDATE rooms SET price = 1400.00 WHERE id = 7;   -- Superior Room
UPDATE rooms SET price = 1650.00 WHERE id = 8;   -- Junior Suite
UPDATE rooms SET price = 3000.00 WHERE id = 9;   -- Penthouse
UPDATE rooms SET price = 1100.00 WHERE id = 10;  -- Business Room

-- Fallback: set price = 800 for any room that still has price = 0
UPDATE rooms SET price = 800.00 WHERE price = 0 OR price IS NULL;

-- ============================================================
-- CREATE FIRST ADMIN (password = "admin123")
-- ============================================================
INSERT IGNORE INTO admins (username, email, mypassword)
VALUES (
    'Admin',
    'admin@hotel.com',
    '$2y$10$8K1p/a0dM5Yk6JpQ3sGvXe1wCrGcNJyZ.eQWVMjVfkXdBQd.yIUHi'
);
-- ^ password hash for: admin123
-- Login at: http://localhost/hotel-booking/admin/login.php

SELECT 'Migration complete!' AS status;
