CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    username VARCHAR(80) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS parties (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_type ENUM('person', 'institution') NOT NULL,
    name VARCHAR(180) NOT NULL,
    identifier VARCHAR(30) NULL,
    phone VARCHAR(40) NULL,
    email VARCHAR(180) NULL,
    address TEXT NULL,
    identity_front_path VARCHAR(255) NULL,
    identity_back_path VARCHAR(255) NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stock_code VARCHAR(40) NOT NULL UNIQUE,
    party_id BIGINT UNSIGNED NOT NULL,
    condition_type ENUM('new', 'refurbished', 'used') NOT NULL,
    brand VARCHAR(100) NOT NULL,
    model VARCHAR(140) NOT NULL,
    storage_size VARCHAR(40) NULL,
    color VARCHAR(80) NULL,
    imei1 VARCHAR(30) NULL,
    imei2 VARCHAR(30) NULL,
    serial_number VARCHAR(100) NULL,
    purchase_price DECIMAL(12,2) NOT NULL DEFAULT 0,
    purchase_date DATE NOT NULL,
    status ENUM('stock', 'sold', 'returned') NOT NULL DEFAULT 'stock',
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_devices_status (status),
    INDEX idx_devices_brand_model (brand, model),
    INDEX idx_devices_imei1 (imei1),
    CONSTRAINT fk_devices_party FOREIGN KEY (party_id) REFERENCES parties(id),
    CONSTRAINT fk_devices_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS checklists (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('device', 'repair') NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    battery_health TINYINT UNSIGNED NULL,
    display_ok TINYINT(1) NOT NULL DEFAULT 0,
    network_ok TINYINT(1) NOT NULL DEFAULT 0,
    front_camera_ok TINYINT(1) NOT NULL DEFAULT 0,
    rear_camera_ok TINYINT(1) NOT NULL DEFAULT 0,
    ringtone_ok TINYINT(1) NOT NULL DEFAULT 0,
    vibration_ok TINYINT(1) NOT NULL DEFAULT 0,
    wifi_ok TINYINT(1) NOT NULL DEFAULT 0,
    bluetooth_ok TINYINT(1) NOT NULL DEFAULT 0,
    rear_flash_ok TINYINT(1) NOT NULL DEFAULT 0,
    bottom_mic_ok TINYINT(1) NOT NULL DEFAULT 0,
    top_mic_ok TINYINT(1) NOT NULL DEFAULT 0,
    charging_ok TINYINT(1) NOT NULL DEFAULT 0,
    box_included TINYINT(1) NOT NULL DEFAULT 0,
    invoice_included TINYINT(1) NOT NULL DEFAULT 0,
    case_condition VARCHAR(80) NULL,
    screen_condition VARCHAR(80) NULL,
    check_notes TEXT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_checklist_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('device', 'repair') NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    category ENUM('extra', 'box', 'invoice', 'before', 'after') NOT NULL,
    stored_path VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_attachments_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sales (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL UNIQUE,
    buyer_type ENUM('person', 'institution') NOT NULL,
    buyer_name VARCHAR(180) NOT NULL,
    buyer_identifier VARCHAR(30) NULL,
    buyer_phone VARCHAR(40) NULL,
    sale_price DECIMAL(12,2) NOT NULL,
    sale_date DATE NOT NULL,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sales_date (sale_date),
    CONSTRAINT fk_sales_device FOREIGN KEY (device_id) REFERENCES devices(id),
    CONSTRAINT fk_sales_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS repairs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    repair_code VARCHAR(40) NOT NULL UNIQUE,
    party_id BIGINT UNSIGNED NOT NULL,
    brand VARCHAR(100) NOT NULL,
    model VARCHAR(140) NOT NULL,
    storage_size VARCHAR(40) NULL,
    color VARCHAR(80) NULL,
    imei VARCHAR(30) NULL,
    serial_number VARCHAR(100) NULL,
    complaint TEXT NOT NULL,
    repair_actions TEXT NULL,
    received_at DATE NOT NULL,
    expected_delivery_date DATE NULL,
    delivered_at DATE NULL,
    status ENUM('received', 'in_progress', 'waiting_parts', 'completed', 'delivered', 'cancelled') NOT NULL DEFAULT 'received',
    quoted_price DECIMAL(12,2) NOT NULL DEFAULT 0,
    final_price DECIMAL(12,2) NOT NULL DEFAULT 0,
    device_password VARCHAR(255) NULL,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_repairs_status (status),
    INDEX idx_repairs_brand_model (brand, model),
    INDEX idx_repairs_imei (imei),
    CONSTRAINT fk_repairs_party FOREIGN KEY (party_id) REFERENCES parties(id),
    CONSTRAINT fk_repairs_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
