CREATE DATABASE IF NOT EXISTS aurelia_crm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE aurelia_crm;
CREATE TABLE IF NOT EXISTS users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) UNIQUE NOT NULL,password_hash VARCHAR(255) NOT NULL,full_name VARCHAR(120) NOT NULL);
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));
CREATE TABLE IF NOT EXISTS settings (id TINYINT UNSIGNED PRIMARY KEY,business_name VARCHAR(150) NOT NULL,logo_path VARCHAR(255),bank_account_number CHAR(8) NOT NULL,sort_code CHAR(8) NOT NULL,smtp_host VARCHAR(190),smtp_port SMALLINT UNSIGNED DEFAULT 587,smtp_username VARCHAR(190),smtp_password VARCHAR(255),smtp_encryption ENUM('tls','ssl','none') DEFAULT 'tls',from_email VARCHAR(190),from_name VARCHAR(150));
INSERT IGNORE INTO settings(id,business_name,logo_path,bank_account_number,sort_code,smtp_port,smtp_encryption) VALUES (1,'Aurelia Beauty Studio',NULL,'12345678','20-46-72',587,'tls');
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_host VARCHAR(190) NULL;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_port SMALLINT UNSIGNED DEFAULT 587;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_username VARCHAR(190) NULL;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_password VARCHAR(255) NULL;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS smtp_encryption ENUM('tls','ssl','none') DEFAULT 'tls';
ALTER TABLE settings ADD COLUMN IF NOT EXISTS from_email VARCHAR(190) NULL;
ALTER TABLE settings ADD COLUMN IF NOT EXISTS from_name VARCHAR(150) NULL;
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.');