-- MySQL initialization schema for liability app
CREATE TABLE IF NOT EXISTS customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_email_phone (email, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS appointments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ms_bookings_id VARCHAR(100) NULL,
    customer_id BIGINT UNSIGNED NOT NULL,
    scheduled_start DATETIME NULL,
    scheduled_end DATETIME NULL,
    location VARCHAR(255) NULL,
    service_name VARCHAR(255) NULL,
    status VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_ms_booking (ms_bookings_id),
    KEY idx_customer (customer_id),
    CONSTRAINT fk_appointments_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS liability_forms (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    appointment_id BIGINT UNSIGNED NOT NULL UNIQUE,
    customer_id BIGINT UNSIGNED NOT NULL,
    device_make VARCHAR(120) NOT NULL,
    device_model VARCHAR(120) NOT NULL,
    device_password VARCHAR(255) NULL,
    problem_description TEXT NOT NULL,
    accessories_left VARCHAR(255) NULL,
    condition_notes TEXT NULL,
    contact_preference ENUM('no_contact','contact_ok') NOT NULL DEFAULT 'contact_ok',
    consent_data_backup TINYINT(1) NOT NULL DEFAULT 0,
    consent_password_handling TINYINT(1) NOT NULL DEFAULT 0,
    signature_name VARCHAR(255) NOT NULL,
    signature_ip VARCHAR(45) NULL,
    signature_timestamp DATETIME NOT NULL,
    version INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_customer (customer_id),
    CONSTRAINT fk_forms_appointment FOREIGN KEY (appointment_id) REFERENCES appointments(id) ON DELETE CASCADE,
    CONSTRAINT fk_forms_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
