CREATE TABLE IF NOT EXISTS users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','sales','service','accounting') NOT NULL DEFAULT 'sales',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

CREATE TABLE IF NOT EXISTS products (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT UNSIGNED NULL,
    supplier_id BIGINT UNSIGNED NULL,
    product_name VARCHAR(255) NOT NULL,
    brand VARCHAR(100) NULL,
    model VARCHAR(140) NULL,
    serial_no VARCHAR(190) NULL,
    condition_status ENUM('Sıfır','Sıfıra Yakın','Yenilenmiş','İkinci El','Arızalı') NOT NULL DEFAULT 'İkinci El',
    quantity INT NOT NULL DEFAULT 1,
    purchase_date DATE NULL,
    purchase_price DECIMAL(12,2) NOT NULL DEFAULT 0,
    expense_total DECIMAL(12,2) NOT NULL DEFAULT 0,
    minimum_sale_price DECIMAL(12,2) NULL,
    target_sale_price DECIMAL(12,2) NULL,
    actual_sale_price DECIMAL(12,2) NULL,
    sale_status ENUM('Stokta','Rezerve','Satıldı','İade') NOT NULL DEFAULT 'Stokta',
    sale_date DATE NULL,
    warranty_end_date DATE NULL,
    shelf_location VARCHAR(100) NULL,
    photo_status ENUM('Hazır Değil','Çekildi','Düzenlendi','Yayınlamaya Hazır') NOT NULL DEFAULT 'Hazır Değil',
    listing_status ENUM('İlan Yok','Hazırlanıyor','Yayında','Pasif') NOT NULL DEFAULT 'İlan Yok',
    invoice_status ENUM('Bekliyor','Kesildi','Gerek Yok') NOT NULL DEFAULT 'Bekliyor',
    sales_channel VARCHAR(100) NULL,
    listing_url VARCHAR(500) NULL,
    notes TEXT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_name(product_name),
    INDEX idx_serial_no(serial_no),
    INDEX idx_sale_status(sale_status),
    INDEX idx_purchase_date(purchase_date),
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_products_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    CONSTRAINT fk_products_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_expenses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    expense_type VARCHAR(120) NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    expense_date DATE NOT NULL,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_expenses_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    action VARCHAR(120) NOT NULL,
    entity_type VARCHAR(100) NULL,
    entity_id BIGINT UNSIGNED NULL,
    details TEXT NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_activity_created(created_at),
    CONSTRAINT fk_activity_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO categories (name, sort_order) VALUES
('Laptop',1),('Masaüstü',2),('Mini PC',3),('Gaming PC',4),('Workstation',5),
('All in One',6),('Monitör',7),('Yazıcı',8),('Tarayıcı',9),('Network',10),
('Sunucu',11),('Parça / Aksesuar',12),('Diğer',13);