-- Създаване на база данни за складова система
CREATE DATABASE IF NOT EXISTS warehouse_system;
USE warehouse_system;

-- Таблица за потребители
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    role ENUM('admin', 'manager', 'employee') DEFAULT 'employee',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Таблица за клиенти
CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(100) NOT NULL,
    contact_person VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(100),
    address TEXT,
    vat_number VARCHAR(20),
    client_type ENUM('individual', 'company') DEFAULT 'company',
    status ENUM('active', 'inactive', 'prospect') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Таблица за категории материали
CREATE TABLE material_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES material_categories(id) ON DELETE SET NULL
);

-- Таблица за материали
CREATE TABLE materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    category_id INT,
    unit VARCHAR(20) NOT NULL,
    unit_price DECIMAL(10, 2),
    min_quantity INT DEFAULT 0,
    current_quantity INT DEFAULT 0,
    supplier VARCHAR(100),
    sku VARCHAR(50) UNIQUE,
    status ENUM('active', 'discontinued', 'out_of_stock') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES material_categories(id) ON DELETE SET NULL
);

-- Таблица за договори
CREATE TABLE contracts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    contract_number VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    value DECIMAL(12, 2),
    status ENUM('draft', 'active', 'expired', 'terminated') DEFAULT 'draft',
    renewal_terms TEXT,
    file_path VARCHAR(255),
    notification_days INT DEFAULT 30,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

-- Таблица за оферти
CREATE TABLE offers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    offer_number VARCHAR(50) UNIQUE NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    total_amount DECIMAL(12, 2),
    status ENUM('draft', 'sent', 'accepted', 'rejected', 'expired') DEFAULT 'draft',
    valid_until DATE,
    file_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

-- Таблица за детайли на офертите
CREATE TABLE offer_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    offer_id INT NOT NULL,
    material_id INT,
    description TEXT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2),
    total_price DECIMAL(12, 2),
    FOREIGN KEY (offer_id) REFERENCES offers(id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE SET NULL
);

-- Таблица за видове протоколи
CREATE TABLE protocol_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    template_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица за протоколи
CREATE TABLE protocols (
    id INT AUTO_INCREMENT PRIMARY KEY,
    protocol_number VARCHAR(50) UNIQUE NOT NULL,
    protocol_type_id INT NOT NULL,
    client_id INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    status ENUM('draft', 'completed', 'cancelled') DEFAULT 'draft',
    completion_date DATE,
    file_path VARCHAR(255),
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (protocol_type_id) REFERENCES protocol_types(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица за детайли на протоколите
CREATE TABLE protocol_details (
    id INT AUTO_INCREMENT PRIMARY KEY,
    protocol_id INT NOT NULL,
    material_id INT,
    description TEXT,
    quantity INT NOT NULL,
    unit_price DECIMAL(10, 2),
    total_price DECIMAL(12, 2),
    action_type ENUM('issued', 'installed', 'removed', 'inspected') DEFAULT 'issued',
    FOREIGN KEY (protocol_id) REFERENCES protocols(id) ON DELETE CASCADE,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE SET NULL
);

-- Таблица за складови движения
CREATE TABLE inventory_movements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    material_id INT NOT NULL,
    movement_type ENUM('in', 'out', 'adjustment') NOT NULL,
    quantity INT NOT NULL,
    reference_type ENUM('purchase', 'protocol', 'adjustment', 'offer') NOT NULL,
    reference_id INT,
    description TEXT,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица за известия
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info', 'warning', 'error', 'success') DEFAULT 'info',
    reference_type ENUM('contract', 'protocol', 'offer', 'inventory') NOT NULL,
    reference_id INT,
    is_read BOOLEAN DEFAULT FALSE,
    due_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица за досиета на клиенти
CREATE TABLE client_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    file_size INT,
    description TEXT,
    uploaded_by INT NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE
);

-- Вмъкване на основни данни
INSERT INTO protocol_types (name, description) VALUES
('Протокол за вложени материали', 'Документ за вложени материали в обект'),
('Протокол за свалена техника', 'Документ за демонтирана техника'),
('Обследване за СОТ', 'Протокол за обследване на система за сигурност'),
('Обследване за видеонаблюдение', 'Протокол за обследване на система за видеонаблюдение'),
('Протокол за инсталация', 'Документ за инсталирано оборудване'),
('Протокол за поддръжка', 'Документ за извършена поддръжка');

INSERT INTO material_categories (name, description) VALUES
('Кабели и проводници', 'Различни видове кабели и проводници'),
('Оборудване за видеонаблюдение', 'Камери, рекордери и аксесоари'),
('Системи за сигурност', 'СОТ, алармени системи'),
('Монтажни материали', 'Конзоли, скоби, крепежни елементи'),
('Електроника', 'Различни електронни компоненти'),
('Софтуер', 'Лицензи и софтуерни продукти');

-- Създаване на индекси за по-бързо търсене
CREATE INDEX idx_materials_category ON materials(category_id);
CREATE INDEX idx_materials_status ON materials(status);
CREATE INDEX idx_protocols_client ON protocols(client_id);
CREATE INDEX idx_protocols_type ON protocols(protocol_type_id);
CREATE INDEX idx_contracts_client ON contracts(client_id);
CREATE INDEX idx_contracts_end_date ON contracts(end_date);
CREATE INDEX idx_offers_client ON offers(client_id);
CREATE INDEX idx_inventory_movements_material ON inventory_movements(material_id);
CREATE INDEX idx_inventory_movements_date ON inventory_movements(created_at);
CREATE INDEX idx_notifications_user ON notifications(user_id);
CREATE INDEX idx_notifications_read ON notifications(is_read);