-- Create the database if not exists and use it
CREATE DATABASE IF NOT EXISTS royal_curtain_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE royal_curtain_db;

-- Drop tables if they exist to start fresh
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS settings;

-- Create settings table
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    admin_whatsapp VARCHAR(50) NOT NULL,
    admin_email VARCHAR(255) NOT NULL,
    admin_address TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert initial admin settings
INSERT INTO settings (id, company_name, admin_whatsapp, admin_email, admin_address)
VALUES (1, 'Royal Curtain', '0788966213', 'sales@royalcurtain.com', 'No. 45, Galle Road, Colombo, Sri Lanka');

-- Create orders table
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(50) NOT NULL UNIQUE,
    customer_name VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    whatsapp VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    address TEXT NOT NULL,
    curtain_type VARCHAR(100) NOT NULL,
    fabric VARCHAR(100) NOT NULL,
    color VARCHAR(100) NOT NULL,
    width DECIMAL(10, 2) NOT NULL,
    height DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    notes TEXT,
    image VARCHAR(255),
    status ENUM('Pending', 'Confirmed', 'Processing', 'Completed', 'Cancelled') NOT NULL 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;

-- Create indexes for performance on search/filter
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_order_no ON orders(order_no);
CREATE INDEX idx_customer_name ON orders(customer_name);

-- Create testimonials table
DROP TABLE IF EXISTS testimonials;
CREATE TABLE testimonials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_name VARCHAR(255) NOT NULL,
    rating INT NOT NULL DEFAULT 5,
    comment TEXT NOT NULL,
    designation VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed initial testimonials
INSERT INTO testimonials (client_name, rating, comment, designation) VALUES 
('Sarah M.', 5, 'The custom curtains from Royal Curtain completely transformed my living room! The fabric quality and fit are unmatched.', 'Homeowner'),
('David K.', 5, 'As an interior designer, I demand perfection. Royal Curtain delivered flawless pinch pleat curtains on time. Highly recommended!', 'Interior Designer'),
('Elena R.', 5, 'Excellent service and great selection. The team helped us measure and choose the perfect blackout curtains for our bedrooms.', 'Villa Owner');