-- =========================================================================
-- V002: Tablas del Grupo B
-- Depende de la V001 del Grupo A (tabla productos con id INTEGER).
-- =========================================================================

CREATE TABLE codigos_qr (
    id             INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    producto_id    INTEGER NOT NULL,
    codigo         VARCHAR(100) NOT NULL UNIQUE
        CHECK (TRIM(codigo) <> ''),
    activo         BOOLEAN NOT NULL DEFAULT TRUE,
    creado_en      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    desactivado_en TIMESTAMPTZ,
    CONSTRAINT fk_codigos_qr_producto FOREIGN KEY (producto_id) REFERENCES productos(id)
);

CREATE INDEX idx_codigos_qr_producto_id ON codigos_qr(producto_id);


CREATE TABLE escaneos_qr (
    id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    qr_id        INTEGER NOT NULL,
    producto_id  INTEGER NOT NULL,
    escaneado_en TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_escaneos_qr_codigo   FOREIGN KEY (qr_id)       REFERENCES codigos_qr(id),
    CONSTRAINT fk_escaneos_qr_producto FOREIGN KEY (producto_id) REFERENCES productos(id)
);

CREATE INDEX idx_escaneos_qr_producto_id  ON escaneos_qr(producto_id);
CREATE INDEX idx_escaneos_qr_escaneado_en ON escaneos_qr(escaneado_en);
CREATE INDEX idx_escaneos_qr_qr_id        ON escaneos_qr(qr_id);

-- producto_id es nullable: NULL = etapa general del establecimiento; con ID = etapa específica del corte.
CREATE TABLE proceso_productivo (
    id             INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    producto_id    INTEGER,
    etapa          VARCHAR(100) NOT NULL
        CHECK (TRIM(etapa) <> ''),
    orden          INTEGER NOT NULL DEFAULT 0
        CHECK (orden >= 0),
    descripcion    TEXT,
    multimedia     TEXT,
    creado_en      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_proceso_productivo_producto FOREIGN KEY (producto_id) REFERENCES productos(id)
);

CREATE INDEX idx_proceso_productivo_orden       ON proceso_productivo(orden);
CREATE INDEX idx_proceso_productivo_producto_id ON proceso_productivo(producto_id);


CREATE TABLE contenido_institucional (
    id             INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    clave          VARCHAR(100) NOT NULL UNIQUE
        CHECK (TRIM(clave) <> ''),
    valor          TEXT NOT NULL
        CHECK (TRIM(valor) <> ''),
    actualizado_en TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- producto_id es nullable: NULL = sello institucional general; con ID = sello específico del corte.
CREATE TABLE certificaciones (
    id             INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    producto_id    INTEGER,
    nombre         VARCHAR(100) NOT NULL
        CHECK (TRIM(nombre) <> ''),
    descripcion    TEXT,
    logo           TEXT,
    activo         BOOLEAN NOT NULL DEFAULT TRUE,
    creado_en      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    actualizado_en TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_certificaciones_producto FOREIGN KEY (producto_id) REFERENCES productos(id)
);

CREATE INDEX idx_certificaciones_producto_id ON certificaciones(producto_id);