PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS parties (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    party_type TEXT NOT NULL CHECK (party_type IN ('person', 'institution')),
    name TEXT NOT NULL,
    identifier TEXT,
    phone TEXT,
    email TEXT,
    address TEXT,
    identity_front_path TEXT,
    identity_back_path TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS devices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stock_code TEXT NOT NULL UNIQUE,
    party_id INTEGER NOT NULL,
    condition_type TEXT NOT NULL CHECK (condition_type IN ('new', 'refurbished', 'used')),
    brand TEXT NOT NULL,
    model TEXT NOT NULL,
    storage_size TEXT,
    color TEXT,
    imei1 TEXT,
    imei2 TEXT,
    serial_number TEXT,
    purchase_price NUMERIC NOT NULL DEFAULT 0,
    purchase_date TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'stock' CHECK (status IN ('stock', 'sold', 'returned')),
    notes TEXT,
    created_by INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (party_id) REFERENCES parties(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_devices_status ON devices(status);
CREATE INDEX IF NOT EXISTS idx_devices_brand_model ON devices(brand, model);
CREATE INDEX IF NOT EXISTS idx_devices_imei1 ON devices(imei1);

CREATE TABLE IF NOT EXISTS checklists (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    entity_type TEXT NOT NULL CHECK (entity_type IN ('device', 'repair')),
    entity_id INTEGER NOT NULL,
    battery_health INTEGER CHECK (battery_health IS NULL OR (battery_health >= 0 AND battery_health <= 100)),
    display_ok INTEGER NOT NULL DEFAULT 0,
    network_ok INTEGER NOT NULL DEFAULT 0,
    front_camera_ok INTEGER NOT NULL DEFAULT 0,
    rear_camera_ok INTEGER NOT NULL DEFAULT 0,
    ringtone_ok INTEGER NOT NULL DEFAULT 0,
    vibration_ok INTEGER NOT NULL DEFAULT 0,
    wifi_ok INTEGER NOT NULL DEFAULT 0,
    bluetooth_ok INTEGER NOT NULL DEFAULT 0,
    rear_flash_ok INTEGER NOT NULL DEFAULT 0,
    bottom_mic_ok INTEGER NOT NULL DEFAULT 0,
    top_mic_ok INTEGER NOT NULL DEFAULT 0,
    charging_ok INTEGER NOT NULL DEFAULT 0,
    box_included INTEGER NOT NULL DEFAULT 0,
    invoice_included INTEGER NOT NULL DEFAULT 0,
    case_condition TEXT,
    screen_condition TEXT,
    check_notes TEXT,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (entity_type, entity_id)
);

CREATE TABLE IF NOT EXISTS attachments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    entity_type TEXT NOT NULL CHECK (entity_type IN ('device', 'repair')),
    entity_id INTEGER NOT NULL,
    category TEXT NOT NULL CHECK (category IN ('extra', 'box', 'invoice', 'before', 'after')),
    stored_path TEXT NOT NULL,
    original_name TEXT NOT NULL,
    mime_type TEXT NOT NULL,
    size_bytes INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_attachments_entity ON attachments(entity_type, entity_id);

CREATE TABLE IF NOT EXISTS sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id INTEGER NOT NULL UNIQUE,
    buyer_type TEXT NOT NULL CHECK (buyer_type IN ('person', 'institution')),
    buyer_name TEXT NOT NULL,
    buyer_identifier TEXT,
    buyer_phone TEXT,
    sale_price NUMERIC NOT NULL,
    sale_date TEXT NOT NULL,
    notes TEXT,
    created_by INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES devices(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_sales_date ON sales(sale_date);

CREATE TABLE IF NOT EXISTS repairs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    repair_code TEXT NOT NULL UNIQUE,
    party_id INTEGER NOT NULL,
    brand TEXT NOT NULL,
    model TEXT NOT NULL,
    storage_size TEXT,
    color TEXT,
    imei TEXT,
    serial_number TEXT,
    complaint TEXT NOT NULL,
    repair_actions TEXT,
    received_at TEXT NOT NULL,
    expected_delivery_date TEXT,
    delivered_at TEXT,
    status TEXT NOT NULL DEFAULT 'received' CHECK (status IN ('received', 'in_progress', 'waiting_parts', 'completed', 'delivered', 'cancelled')),
    quoted_price NUMERIC NOT NULL DEFAULT 0,
    final_price NUMERIC NOT NULL DEFAULT 0,
    device_password TEXT,
    notes TEXT,
    created_by INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (party_id) REFERENCES parties(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_repairs_status ON repairs(status);
CREATE INDEX IF NOT EXISTS idx_repairs_brand_model ON repairs(brand, model);
CREATE INDEX IF NOT EXISTS idx_repairs_imei ON repairs(imei);
