CREATE DATABASE IF NOT EXISTS aurelia_crm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE aurelia_crm;
CREATE TABLE IF NOT EXISTS customers (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,full_name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL,phone VARCHAR(30) NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,INDEX idx_customers_name(full_name));
CREATE TABLE IF NOT EXISTS services (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,duration_minutes SMALLINT UNSIGNED NOT NULL,price DECIMAL(10,2) NOT NULL,description VARCHAR(500) NOT NULL,CHECK(duration_minutes>0),CHECK(price>=0));
CREATE TABLE IF NOT EXISTS appointments (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_id BIGINT UNSIGNED NOT NULL,start_at DATETIME NOT NULL,end_at DATETIME NOT NULL,status ENUM('pending','confirmed','completed','cancelled') DEFAULT 'confirmed',FOREIGN KEY(customer_id) REFERENCES customers(id),INDEX idx_appointments_start(start_at));
CREATE TABLE IF NOT EXISTS appointment_services (appointment_id BIGINT UNSIGNED NOT NULL,service_id BIGINT UNSIGNED NOT NULL,price_at_booking DECIMAL(10,2) NOT NULL,duration_at_booking SMALLINT UNSIGNED NOT NULL,PRIMARY KEY(appointment_id,service_id),FOREIGN KEY(appointment_id) REFERENCES appointments(id) ON DELETE CASCADE,FOREIGN KEY(service_id) REFERENCES services(id));
CREATE TABLE IF NOT EXISTS invoices (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,appointment_id BIGINT UNSIGNED UNIQUE NOT NULL,invoice_number VARCHAR(30) UNIQUE NOT NULL,status ENUM('draft','sent','paid','void') DEFAULT 'draft',total DECIMAL(10,2) NOT NULL,sent_at DATETIME,FOREIGN KEY(appointment_id) REFERENCES appointments(id));
INSERT IGNORE INTO customers(full_name,email,phone) VALUES ('Sophie Carter','sophie@example.com','07700 900124'),('Amelia Brooks','amelia@example.com','07700 900582'),('Maya Thompson','maya@example.com','07700 900337');
INSERT IGNORE INTO services(name,duration_minutes,price,description) VALUES ('Signature Facial',60,85,'Tailored facial with massage and mask.'),('Gel Manicure',45,42,'Cuticle care and long-wear gel colour.'),('Brow Shape & Tint',30,28,'Precision shaping and custom tint.'),('Full Body Massage',75,95,'Restorative massage with aromatic oils.');
