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,
    deleted_at TEXT,
    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,
    listed_price NUMERIC NOT NULL DEFAULT 0,
    purchase_date TEXT NOT NULL,
    is_public INTEGER NOT NULL DEFAULT 0 CHECK (is_public IN (0, 1)),
    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,
    proximity_sensor_ok INTEGER NOT NULL DEFAULT 0,
    ambient_light_sensor_ok INTEGER NOT NULL DEFAULT 0,
    biometric_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);

CREATE TABLE IF NOT EXISTS user_permissions (
    user_id INTEGER PRIMARY KEY,
    role TEXT NOT NULL DEFAULT 'staff' CHECK (role IN ('admin', 'staff')),
    is_active INTEGER NOT NULL DEFAULT 1,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS service_providers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    phone TEXT,
    tax_number TEXT,
    address TEXT,
    notes TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS repair_expenses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    repair_id INTEGER NOT NULL,
    expense_type TEXT NOT NULL CHECK (expense_type IN ('part', 'external_service', 'other')),
    description TEXT NOT NULL,
    quantity NUMERIC NOT NULL DEFAULT 1,
    unit_cost NUMERIC NOT NULL DEFAULT 0,
    expense_date TEXT NOT NULL,
    provider_id INTEGER,
    created_by INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TEXT,
    FOREIGN KEY (repair_id) REFERENCES repairs(id),
    FOREIGN KEY (provider_id) REFERENCES service_providers(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_repair_expenses_repair ON repair_expenses(repair_id);

CREATE TABLE IF NOT EXISTS sale_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    legacy_sale_id INTEGER,
    device_id INTEGER NOT NULL,
    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,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TEXT,
    FOREIGN KEY (device_id) REFERENCES devices(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_sale_history_device ON sale_history(device_id);

CREATE TABLE IF NOT EXISTS device_returns (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    device_id INTEGER NOT NULL,
    return_type TEXT NOT NULL CHECK (return_type IN ('purchase_return', 'sale_return')),
    sale_history_id INTEGER,
    counterparty_name TEXT,
    amount NUMERIC NOT NULL DEFAULT 0,
    reason TEXT NOT NULL,
    return_date TEXT NOT NULL,
    created_by INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TEXT,
    FOREIGN KEY (device_id) REFERENCES devices(id),
    FOREIGN KEY (sale_history_id) REFERENCES sale_history(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_device_returns_device ON device_returns(device_id);

CREATE TABLE IF NOT EXISTS financial_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    reference_key TEXT NOT NULL UNIQUE,
    entry_date TEXT NOT NULL,
    direction TEXT NOT NULL CHECK (direction IN ('income', 'expense')),
    category TEXT NOT NULL CHECK (category IN (
        'device_purchase', 'device_sale', 'purchase_return', 'sale_return',
        'repair_income', 'repair_part', 'repair_service', 'repair_other',
        'rent', 'shipping', 'tax', 'utilities', 'salary', 'general'
    )),
    amount NUMERIC NOT NULL DEFAULT 0,
    description TEXT NOT NULL,
    entity_type TEXT,
    entity_id INTEGER,
    related_id INTEGER,
    created_by INTEGER,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TEXT,
    FOREIGN KEY (created_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_financial_entries_date ON financial_entries(entry_date);
CREATE INDEX IF NOT EXISTS idx_financial_entries_entity ON financial_entries(entity_type, entity_id);

CREATE TABLE IF NOT EXISTS activity_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    entity_type TEXT NOT NULL CHECK (entity_type IN ('device', 'repair', 'accounting')),
    entity_id INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    title TEXT NOT NULL,
    details TEXT,
    from_status TEXT,
    to_status TEXT,
    related_id INTEGER,
    metadata TEXT,
    reversible INTEGER NOT NULL DEFAULT 0,
    created_by INTEGER,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reversed_at TEXT,
    reversed_by INTEGER,
    FOREIGN KEY (created_by) REFERENCES users(id),
    FOREIGN KEY (reversed_by) REFERENCES users(id)
);

CREATE INDEX IF NOT EXISTS idx_activity_logs_entity ON activity_logs(entity_type, entity_id, id);

CREATE TABLE IF NOT EXISTS system_meta (
    meta_key TEXT PRIMARY KEY,
    meta_value TEXT,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
