-- ============================================================
-- MODUL INSTALARI - adaugat la baza evidenta_deconturi
-- v2 - fara FK pe employees.id (evita incompatibilitate de tip)
-- ============================================================

USE evidenta_deconturi;

CREATE TABLE IF NOT EXISTS inst_articole (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tip ENUM('manopera','material') NOT NULL,
    denumire VARCHAR(255) NOT NULL,
    um VARCHAR(20) NOT NULL DEFAULT 'buc',
    pret_unitar DECIMAL(10,2) DEFAULT NULL,
    activ TINYINT(1) NOT NULL DEFAULT 1,
    ordine INT NOT NULL DEFAULT 0,
    creat_la TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modificat_la TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Istoric modificari preturi (fara FK pe employees - evita incompatibilitate INT/INT UNSIGNED)
CREATE TABLE IF NOT EXISTS inst_preturi_istoric (
    id INT AUTO_INCREMENT PRIMARY KEY,
    articol_id INT NOT NULL,
    pret_vechi DECIMAL(10,2),
    pret_nou DECIMAL(10,2),
    modificat_de INT,              -- employees.id, referinta logica fara constraint
    modificat_de_nume VARCHAR(255),-- snapshot nume, nu depinde de FK
    modificat_la TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (articol_id) REFERENCES inst_articole(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Rapoarte zilnice
CREATE TABLE IF NOT EXISTS inst_rapoarte (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lucrare_id INT NOT NULL,
    employee_id INT NOT NULL,
    data_lucru DATE NOT NULL,
    observatii TEXT,
    creat_la TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    modificat_la TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_lucrare_angajat_data (lucrare_id, employee_id, data_lucru),
    FOREIGN KEY (lucrare_id) REFERENCES lucrari(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS inst_raport_articole (
    id INT AUTO_INCREMENT PRIMARY KEY,
    raport_id INT NOT NULL,
    articol_id INT NOT NULL,
    cantitate DECIMAL(10,2) NOT NULL DEFAULT 0,
    pret_unitar_snapshot DECIMAL(10,2) DEFAULT NULL,
    FOREIGN KEY (raport_id) REFERENCES inst_rapoarte(id) ON DELETE CASCADE,
    FOREIGN KEY (articol_id) REFERENCES inst_articole(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS inst_raport_poze (
    id INT AUTO_INCREMENT PRIMARY KEY,
    raport_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    descriere VARCHAR(500),
    creat_la TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (raport_id) REFERENCES inst_rapoarte(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Date initiale
INSERT INTO inst_articole (tip, denumire, um, pret_unitar, ordine) VALUES
('manopera','Instalare camera <20m','buc',75.00,1),
('manopera','Instalare camera 20-50m','buc',125.00,2),
('manopera','Instalare PTZ','buc',75.00,3),
('manopera','Instalare cutie','buc',100.00,4),
('manopera','Instalare cablu UTP','m',3.50,5),
('manopera','Instalare canal cablu/tub/copex','m',3.50,6),
('manopera','Instalare cablu alimentare','m',3.50,7),
('manopera','Instalare AP','buc',75.00,8),
('manopera','Instalare rack','buc',100.00,9),
('manopera','Instalare FO','m',3.50,10),
('manopera','Instalare switch','buc',20.00,11),
('manopera','Instalare tripla+stecher','buc',20.00,12),
('manopera','Instalare alimentator','buc',10.00,13),
('manopera','Instalare patchcord','buc',10.00,14),
('manopera','Sudura FO','buc',30.00,15),
('manopera','Instalare caseta sudura','buc',30.00,16),
('manopera','Verificare cablu UTP/FO','buc',10.00,17),
('manopera','Instalare PDU','buc',15.00,18),
('manopera','Instalare mediaconvertor','buc',10.00,19),
('manopera','Refacere mufa RJ45','buc',5.00,20),
('manopera','Relocare camera/AP','buc',75.00,21),
('manopera','Materiale marunte','buc',NULL,22),
('manopera','Alte operatiuni','buc',NULL,23),
('material','Switch','buc',NULL,1),
('material','Access point','buc',NULL,2),
('material','Camera','buc',NULL,3),
('material','Router','buc',NULL,4),
('material','Rack','buc',NULL,5),
('material','Cablu UTP','m',NULL,6),
('material','Fibra 12 fire','m',NULL,7),
('material','Caseta sudura','buc',NULL,8),
('material','Mufe RJ45','buc',NULL,9),
('material','Pigtail','buc',NULL,10),
('material','Mediaconvertor','buc',NULL,11);
