CREATE DATABASE IF NOT EXISTS calidadExt;
USE calidadExt;

-- Reference Tables
CREATE TABLE IF NOT EXISTS tipoUsr (
    idTipoUsr INT AUTO_INCREMENT PRIMARY KEY,
    nomTipoUsr VARCHAR(11) NOT NULL
);

-- Note: passUsr was INT in diagram, but best practice is string for hashing.
CREATE TABLE IF NOT EXISTS Usuario (
    idUsr INT AUTO_INCREMENT PRIMARY KEY,
    nomUsr VARCHAR(35) NOT NULL,
    fullNameUsr VARCHAR(75) NOT NULL,
    idTipoUsr INT NOT NULL,
    passUsr VARCHAR(255) NOT NULL,
    FOREIGN KEY (idTipoUsr) REFERENCES tipoUsr(idTipoUsr) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS Cliente (
    idCliente INT AUTO_INCREMENT PRIMARY KEY,
    nombreCliente VARCHAR(35) NOT NULL,
    passCliente VARCHAR(255),
    mailCliente VARCHAR(55)
);

-- Note: Diagram lists nomProd and mailProd as INT(11) which is likely a mistake. Using VARCHAR.
CREATE TABLE IF NOT EXISTS Productor (
    idProd INT AUTO_INCREMENT PRIMARY KEY,
    nomProd VARCHAR(35) NOT NULL,
    csg VARCHAR(35),
    mailProd VARCHAR(55)
);

CREATE TABLE IF NOT EXISTS Especie (
    idEspecie INT AUTO_INCREMENT PRIMARY KEY,
    nomEspecie VARCHAR(35) NOT NULL
);

CREATE TABLE IF NOT EXISTS tipoDefecto (
    idTipoDef INT AUTO_INCREMENT PRIMARY KEY,
    nomTipoDef VARCHAR(15) NOT NULL
);

CREATE TABLE IF NOT EXISTS Defectos (
    idDefecto INT AUTO_INCREMENT PRIMARY KEY,
    idTipoDef INT,
    idEspecie INT,
    nomDefecto VARCHAR(35) NOT NULL,
    FOREIGN KEY (idTipoDef) REFERENCES tipoDefecto(idTipoDef),
    FOREIGN KEY (idEspecie) REFERENCES Especie(idEspecie)
);

CREATE TABLE IF NOT EXISTS Calibre (
    idCalibre INT AUTO_INCREMENT PRIMARY KEY,
    idEspecie INT,
    nCalibre VARCHAR(15) NOT NULL,
    FOREIGN KEY (idEspecie) REFERENCES Especie(idEspecie)
);

CREATE TABLE IF NOT EXISTS tipoCategoria (
    idTipCat INT AUTO_INCREMENT PRIMARY KEY,
    nomTipCat VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS Categoria (
    idCategoria INT AUTO_INCREMENT PRIMARY KEY,
    idTipCat INT,
    nomCategoria VARCHAR(20) NOT NULL,
    FOREIGN KEY (idTipCat) REFERENCES tipoCategoria(idTipCat)
);

CREATE TABLE IF NOT EXISTS IndiceMadurez (
    idMadurez INT AUTO_INCREMENT PRIMARY KEY,
    idEspecie INT,
    nomMadurex VARCHAR(35) NOT NULL,
    FOREIGN KEY (idEspecie) REFERENCES Especie(idEspecie)
);

-- Main Header Table
CREATE TABLE IF NOT EXISTS headerCalidad (
    idHead INT AUTO_INCREMENT PRIMARY KEY,
    idCliente INT,
    idUsr INT,
    idProd INT,
    lote INT,
    Bins INT,
    huerto VARCHAR(35),
    um VARCHAR(35),
    codigo VARCHAR(10),
    variedad VARCHAR(25),
    sdp VARCHAR(10),
    csg VARCHAR(35),
    fechaRecepcion DATE,
    fechaCosecha DATE,
    tAmbiente FLOAT,
    cuartel VARCHAR(25),
    nMuestras INT,
    hora TIME,
    tPulpa FLOAT,
    pesoMayor FLOAT,
    pesoMenor FLOAT,
    pesoMedio FLOAT,
    tempUno FLOAT,
    tempDos FLOAT,
    tempTres FLOAT,
    publicado INT DEFAULT 0,
    observaciones VARCHAR(255),
    responsable VARCHAR(35),
    idEspecie INT,
    sincronizado_erp INT DEFAULT 0,
    msg_sincronizacion VARCHAR(255),
    FOREIGN KEY (idCliente) REFERENCES Cliente(idCliente),
    FOREIGN KEY (idUsr) REFERENCES Usuario(idUsr),
    FOREIGN KEY (idProd) REFERENCES Productor(idProd),
    FOREIGN KEY (idEspecie) REFERENCES Especie(idEspecie)
);

-- Detail/Junction Tables
CREATE TABLE IF NOT EXISTS DetDefectos (
    idDetDef INT AUTO_INCREMENT PRIMARY KEY,
    idHead INT NOT NULL,
    idDefecto INT NOT NULL,
    frutoDefecto INT,
    FOREIGN KEY (idHead) REFERENCES headerCalidad(idHead) ON DELETE CASCADE,
    FOREIGN KEY (idDefecto) REFERENCES Defectos(idDefecto)
);

CREATE TABLE IF NOT EXISTS detalleCalibre (
    idDetCal INT AUTO_INCREMENT PRIMARY KEY,
    cantidadCal INT,
    porcentajeCal DECIMAL(5,2),
    idCalibre INT NOT NULL,
    idHead INT NOT NULL,
    FOREIGN KEY (idHead) REFERENCES headerCalidad(idHead) ON DELETE CASCADE,
    FOREIGN KEY (idCalibre) REFERENCES Calibre(idCalibre)
);

CREATE TABLE IF NOT EXISTS DetCate (
    idDetCate INT AUTO_INCREMENT PRIMARY KEY,
    idHead INT NOT NULL,
    idCategoria INT NOT NULL,
    cantFrutos DECIMAL(5,2),
    FOREIGN KEY (idHead) REFERENCES headerCalidad(idHead) ON DELETE CASCADE,
    FOREIGN KEY (idCategoria) REFERENCES Categoria(idCategoria)
);

CREATE TABLE IF NOT EXISTS tipoCategoria (
    idTipCat INT AUTO_INCREMENT PRIMARY KEY,
    nomTipCat VARCHAR(35) NOT NULL
);

CREATE TABLE IF NOT EXISTS detMadurez (
    idDetMad INT AUTO_INCREMENT PRIMARY KEY,
    idHead INT NOT NULL,
    idMadurez INT NOT NULL,
    cantFrutosM INT,
    val_big DECIMAL(5,2),
    val_medium DECIMAL(5,2),
    val_small DECIMAL(5,2),
    FOREIGN KEY (idHead) REFERENCES headerCalidad(idHead) ON DELETE CASCADE,
    FOREIGN KEY (idMadurez) REFERENCES IndiceMadurez(idMadurez)
);

CREATE TABLE IF NOT EXISTS Brix (
    idBrix INT AUTO_INCREMENT PRIMARY KEY,
    nomBrix VARCHAR(25) NOT NULL
);

CREATE TABLE IF NOT EXISTS detBrix (
    idDetBrix INT AUTO_INCREMENT PRIMARY KEY,
    idHead INT NOT NULL,
    idBrix INT NOT NULL,
    noFrutos INT,
    val_big DECIMAL(5,2),
    val_medium DECIMAL(5,2),
    val_small DECIMAL(5,2),
    FOREIGN KEY (idHead) REFERENCES headerCalidad(idHead) ON DELETE CASCADE,
    FOREIGN KEY (idBrix) REFERENCES Brix(idBrix)
);

CREATE TABLE IF NOT EXISTS Color (
    idColor INT AUTO_INCREMENT PRIMARY KEY,
    idEspecie INT,
    nomColor VARCHAR(35) NOT NULL,
    FOREIGN KEY (idEspecie) REFERENCES Especie(idEspecie)
);

CREATE TABLE IF NOT EXISTS detColor (
    idDetColor INT AUTO_INCREMENT PRIMARY KEY,
    idHead INT NOT NULL,
    idColor INT NOT NULL,
    porcColor DECIMAL(5,2),
    FOREIGN KEY (idHead) REFERENCES headerCalidad(idHead) ON DELETE CASCADE,
    FOREIGN KEY (idColor) REFERENCES Color(idColor)
);

CREATE TABLE IF NOT EXISTS Firmeza (
    idFirmeza INT AUTO_INCREMENT PRIMARY KEY,
    nomFirmeza VARCHAR(35) NOT NULL
);

CREATE TABLE IF NOT EXISTS detFirmeza (
    idDetFirmeza INT AUTO_INCREMENT PRIMARY KEY,
    idHead INT NOT NULL,
    idFirmeza INT NOT NULL,
    porcDetFirmeza DECIMAL(5,2),
    FOREIGN KEY (idHead) REFERENCES headerCalidad(idHead) ON DELETE CASCADE,
    FOREIGN KEY (idFirmeza) REFERENCES Firmeza(idFirmeza)
);

-- Initial Mock Data
INSERT INTO tipoUsr (nomTipoUsr) VALUES ('Admin'), ('Gestor'), ('Inspector');
-- Password is '123'
INSERT INTO Usuario (nomUsr, idTipoUsr, passUsr) VALUES ('admin','administrador', 1, '123'), ('pepe','José Pedro', 3, '123');
INSERT INTO Cliente (nombreCliente, passCliente, mailCliente) VALUES ('Agricola SA', '123', 'contacto@agricolasa.com');
INSERT INTO Productor (nomProd, csg, mailProd) VALUES ('Fundo Los Olivos', '12345', 'fundo@olivos.com');
INSERT IGNORE INTO Especie (idEspecie, nomEspecie) VALUES (1, 'Ciruela'), (2, 'Cereza'), (3, 'Kiwi');
INSERT IGNORE INTO tipoDefecto (idTipoDef, nomTipoDef) VALUES (1, 'Condición'), (2, 'Calidad');
INSERT INTO Defectos (idTipoDef, idEspecie, nomDefecto) VALUES (1, 1, 'Fruto sin pedicelos'), (1, 1, 'Residuos visuales'), (1, 1, 'Residuos extranos'), (1, 1, 'Tierra'), (1, 1, 'Presencia insectos'), (1, 1, 'Pedicelo cafe'), (1, 1, 'Mancha roce'), (1, 1, 'Daño tipo trips'), (1, 1, 'Golpe de sol'), (1, 1, 'Frutos deformes'), (1, 1, 'Frutos dobles'), (1, 1, 'Herida cicatrizada'), (1, 1, 'Russet'), (1, 1, 'Falta de color'), (1, 1, 'Sutura profunda'), (1, 1, 'Fruto deshidratado'), (1, 1, 'Precalibre'), (1, 1, 'Pudricion negra'), (1, 1, 'Pudricion parda'), (1, 1, 'Daño insecto'), (1, 1, 'Daño pajaro'), (1, 1, 'Virosis'), (1, 1, 'Hijuelos'), (1, 1, 'Mancha parda'), (1, 1, 'Herida abierta'), (1, 1, 'Partiduras leves'), (1, 1, 'Fruto sobremaduro'), (1, 1, 'Machucones'), (1, 1, 'Fruto blando'), (1, 1, 'Medialuna 50'), (1, 1, 'Partiduras severas'), (1, 1, 'Pedicelos deshidratados'), (1, 1, 'Pitting'), (1, 1, 'Indicios pudricion');
INSERT INTO Calibre (idEspecie, nCalibre) VALUES (1, 'Super Giant'), (1, 'Giant'), (1, 'Super Jumbo'), (1, 'Jumbo'), (1, 'Extra Large'), (1, 'Large'), (1, 'Pre Calibre');
INSERT IGNORE INTO tipoCategoria (idTipCat, nomTipCat) VALUES (1, 'General');
INSERT INTO Categoria (idTipCat, nomCategoria) VALUES (1, 'Cat 1'), (1, 'Cat 2'), (1, 'Descarte');
INSERT INTO IndiceMadurez (idEspecie, nomMadurex) VALUES (1, 'Pinton'), (1, 'Maduro'), (1, 'Sobre maduro');
INSERT INTO Color (idEspecie, nomColor) VALUES (1, 'Rojo Claro'), (1, 'Rojo'), (1, 'Rojo Caoba'), (1, 'Caoba'), (1, 'Caoba Oscuro'), (1, 'Negro');
INSERT INTO Firmeza (nomFirmeza) VALUES ('Blanda'), ('Sensible'), ('Semifirme'), ('Firme'), ('Muy Firme');

-- Export Views
CREATE OR REPLACE VIEW v_export_defectos AS
SELECT 
    h.idHead,
    h.lote,
    dd.idDetDef,
    dd.frutoDefecto AS dade_podade,
    CASE d.nomDefecto
        WHEN 'Pudricion' THEN 213
        WHEN 'Machucon' THEN 308
        WHEN 'Daño por frio' THEN 203
        WHEN 'Fruto sin pedicelos' THEN 4
        WHEN 'Residuos visuales' THEN 5
        WHEN 'Residuos extranos' THEN 6
        WHEN 'Tierra' THEN 7
        WHEN 'Presencia insectos' THEN 8
        WHEN 'Pedicelo cafe' THEN 200
        WHEN 'Mancha roce' THEN 201
        WHEN 'Daño tipo trips' THEN 202
        WHEN 'Golpe de sol' THEN 203
        WHEN 'Frutos deformes' THEN 204
        WHEN 'Frutos dobles' THEN 205
        WHEN 'Herida cicatrizada' THEN 206
        WHEN 'Russet' THEN 207
        WHEN 'Falta de color' THEN 208
        WHEN 'Sutura profunda' THEN 209
        WHEN 'Fruto deshidratado' THEN 210
        WHEN 'Precalibre' THEN 211
        WHEN 'Pudricion negra' THEN 213
        WHEN 'Pudricion parda' THEN 214
        WHEN 'Daño insecto' THEN 300
        WHEN 'Daño pajaro' THEN 301
        WHEN 'Virosis' THEN 302
        WHEN 'Hijuelos' THEN 303
        WHEN 'Mancha parda' THEN 304
        WHEN 'Herida abierta' THEN 305
        WHEN 'Partiduras leves' THEN 306
        WHEN 'Fruto sobremaduro' THEN 307
        WHEN 'Machucones' THEN 308
        WHEN 'Fruto blando' THEN 309
        WHEN 'Medialuna 50' THEN 310
        WHEN 'Partiduras severas' THEN 311
        WHEN 'Pedicelos deshidratados' THEN 313
        WHEN 'Pitting' THEN 314
        WHEN 'Indicios pudricion' THEN 315
        ELSE NULL
    END AS dade_codigo
FROM headerCalidad h
JOIN DetDefectos dd ON h.idHead = dd.idHead
JOIN Defectos d ON dd.idDefecto = d.idDefecto;

CREATE OR REPLACE VIEW v_export_calibres AS
SELECT 
    h.idHead,
    h.lote,
    dc.idDetCal,
    dc.porcentajeCal AS disca_porren,
    CASE c.nCalibre
        WHEN 'Super Giant' THEN '1SG'
        WHEN 'Giant' THEN '2G'
        WHEN 'Super Jumbo' THEN '3SJ'
        WHEN 'Jumbo' THEN '4J'
        WHEN 'Extra Large' THEN '5XL'
        WHEN 'Large' THEN '6L'
        WHEN 'Pre Calibre' THEN '7PC'
        ELSE NULL
    END AS disca_grupca
FROM headerCalidad h
JOIN detalleCalibre dc ON h.idHead = dc.idHead
JOIN Calibre c ON dc.idCalibre = c.idCalibre;

CREATE OR REPLACE VIEW v_export_color AS
SELECT 
    h.idHead,
    h.lote,
    dc.idDetColor,
    dc.porcColor AS ccca_porcen,
    CASE c.nomColor
        WHEN 'Rojo Claro' THEN 0
        WHEN 'Rojo' THEN 1
        WHEN 'Rojo Caoba' THEN 3
        WHEN 'Caoba' THEN 4
        WHEN 'Caoba Oscuro' THEN 5
        WHEN 'Negro' THEN 6
        ELSE NULL
    END AS Cate_codigo
FROM headerCalidad h
JOIN detColor dc ON h.idHead = dc.idHead
JOIN Color c ON dc.idColor = c.idColor;