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,
    deleted_at TIMESTAMP 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,
    listed_price DECIMAL(12,2) NOT NULL DEFAULT 0,
    purchase_date DATE NOT NULL,
    is_public TINYINT(1) NOT NULL DEFAULT 0,
    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,
    proximity_sensor_ok TINYINT(1) NOT NULL DEFAULT 0,
    ambient_light_sensor_ok TINYINT(1) NOT NULL DEFAULT 0,
    biometric_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;

CREATE TABLE IF NOT EXISTS user_permissions (
    user_id BIGINT UNSIGNED PRIMARY KEY,
    role ENUM('admin', 'staff') NOT NULL DEFAULT 'staff',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_permissions_user FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS service_providers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(180) NOT NULL,
    phone VARCHAR(40) NULL,
    tax_number VARCHAR(40) NULL,
    address TEXT NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS repair_expenses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    repair_id BIGINT UNSIGNED NOT NULL,
    expense_type ENUM('part', 'external_service', 'other') NOT NULL,
    description VARCHAR(500) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    expense_date DATE NOT NULL,
    provider_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TIMESTAMP NULL,
    INDEX idx_repair_expenses_repair (repair_id),
    CONSTRAINT fk_repair_expenses_repair FOREIGN KEY (repair_id) REFERENCES repairs(id),
    CONSTRAINT fk_repair_expenses_provider FOREIGN KEY (provider_id) REFERENCES service_providers(id),
    CONSTRAINT fk_repair_expenses_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sale_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    legacy_sale_id BIGINT UNSIGNED NULL,
    device_id BIGINT UNSIGNED NOT NULL,
    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 NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TIMESTAMP NULL,
    INDEX idx_sale_history_device (device_id),
    CONSTRAINT fk_sale_history_device FOREIGN KEY (device_id) REFERENCES devices(id),
    CONSTRAINT fk_sale_history_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS device_returns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL,
    return_type ENUM('purchase_return', 'sale_return') NOT NULL,
    sale_history_id BIGINT UNSIGNED NULL,
    counterparty_name VARCHAR(180) NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    reason TEXT NOT NULL,
    return_date DATE NOT NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TIMESTAMP NULL,
    INDEX idx_device_returns_device (device_id),
    CONSTRAINT fk_device_returns_device FOREIGN KEY (device_id) REFERENCES devices(id),
    CONSTRAINT fk_device_returns_sale FOREIGN KEY (sale_history_id) REFERENCES sale_history(id),
    CONSTRAINT fk_device_returns_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS financial_entries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference_key VARCHAR(190) NOT NULL UNIQUE,
    entry_date DATE NOT NULL,
    direction ENUM('income', 'expense') NOT NULL,
    category ENUM(
        'device_purchase', 'device_sale', 'purchase_return', 'sale_return',
        'repair_income', 'repair_part', 'repair_service', 'repair_other',
        'rent', 'shipping', 'tax', 'utilities', 'salary', 'general'
    ) NOT NULL,
    amount DECIMAL(12,2) NOT NULL DEFAULT 0,
    description VARCHAR(500) NOT NULL,
    entity_type VARCHAR(40) NULL,
    entity_id BIGINT UNSIGNED NULL,
    related_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TIMESTAMP NULL,
    INDEX idx_financial_entries_date (entry_date),
    INDEX idx_financial_entries_entity (entity_type, entity_id),
    CONSTRAINT fk_financial_entries_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('device', 'repair', 'accounting') NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    event_type VARCHAR(80) NOT NULL,
    title VARCHAR(240) NOT NULL,
    details TEXT NULL,
    from_status VARCHAR(80) NULL,
    to_status VARCHAR(80) NULL,
    related_id BIGINT UNSIGNED NULL,
    metadata LONGTEXT NULL,
    reversible TINYINT(1) NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TIMESTAMP NULL,
    reversed_by BIGINT UNSIGNED NULL,
    INDEX idx_activity_logs_entity (entity_type, entity_id, id),
    CONSTRAINT fk_activity_logs_user FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_activity_logs_reversed_user FOREIGN KEY (reversed_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS system_meta (
    meta_key VARCHAR(190) PRIMARY KEY,
    meta_value TEXT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
