shopdb/sql/udctables.sql

-- ============================================================================
-- UDC Log Parser Database Schema
-- Stores parsed data from UDC (Universal Data Collector) log files
-- Created: 2025-12-12
-- ============================================================================

-- Sessions table - tracks each log file imported
CREATE TABLE IF NOT EXISTS udcsessions (
    sessionid INT AUTO_INCREMENT PRIMARY KEY,
    machineid INT,
    machinenumber VARCHAR(20),
    logfilename VARCHAR(255) NOT NULL,
    sessionstart DATETIME,
    sessionend DATETIME,
    recordcount INT DEFAULT 0,
    dateadded DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_machineid (machineid),
    INDEX idx_machinenumber (machinenumber),
    INDEX idx_sessionstart (sessionstart),
    UNIQUE INDEX idx_logfilename (logfilename(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Parts table - tracks each part run (serial number cycle)
CREATE TABLE IF NOT EXISTS udcparts (
    partrunid INT AUTO_INCREMENT PRIMARY KEY,
    sessionid INT NOT NULL,
    machineid INT,
    partnumber VARCHAR(50),
    opernumber VARCHAR(20),
    serialnumber VARCHAR(50),
    programname VARCHAR(50),
    jobnumber VARCHAR(50),
    badgenumber VARCHAR(20),
    programstart DATETIME,
    programend DATETIME,
    cycletime INT,
    changeover INT,
    measurementcount INT DEFAULT 0,
    manualcount INT DEFAULT 0,
    probecount INT DEFAULT 0,
    ootcount INT DEFAULT 0,
    dateadded DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sessionid (sessionid),
    INDEX idx_machineid (machineid),
    INDEX idx_serialnumber (serialnumber),
    INDEX idx_partnumber (partnumber),
    INDEX idx_jobnumber (jobnumber),
    INDEX idx_badgenumber (badgenumber),
    INDEX idx_programstart (programstart),
    CONSTRAINT fk_udcparts_session FOREIGN KEY (sessionid) REFERENCES udcsessions(sessionid) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Measurements table - all PROCESSDATA, TOOLDATA, MACHINEDATA entries
CREATE TABLE IF NOT EXISTS udcmeasurements (
    measurementid INT AUTO_INCREMENT PRIMARY KEY,
    partrunid INT,
    sessionid INT NOT NULL,
    eventtime DATETIME,
    eventtype VARCHAR(20),
    method VARCHAR(20),
    dimid VARCHAR(20),
    description VARCHAR(255),
    seqnumber INT,
    minval DECIMAL(12,6),
    maxval DECIMAL(12,6),
    actualval DECIMAL(12,6),
    deviation DECIMAL(12,6),
    oot TINYINT DEFAULT 0,
    INDEX idx_partrunid (partrunid),
    INDEX idx_sessionid (sessionid),
    INDEX idx_eventtime (eventtime),
    INDEX idx_method (method),
    INDEX idx_oot (oot),
    CONSTRAINT fk_udcmeasurements_partrun FOREIGN KEY (partrunid) REFERENCES udcparts(partrunid) ON DELETE CASCADE,
    CONSTRAINT fk_udcmeasurements_session FOREIGN KEY (sessionid) REFERENCES udcsessions(sessionid) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Events table - item crossings, messages, manual requests
CREATE TABLE IF NOT EXISTS udcevents (
    eventid INT AUTO_INCREMENT PRIMARY KEY,
    partrunid INT,
    sessionid INT NOT NULL,
    eventtime DATETIME,
    eventtype VARCHAR(30),
    itemnumber VARCHAR(20),
    description TEXT,
    INDEX idx_partrunid (partrunid),
    INDEX idx_sessionid (sessionid),
    INDEX idx_eventtime (eventtime),
    INDEX idx_eventtype (eventtype),
    CONSTRAINT fk_udcevents_partrun FOREIGN KEY (partrunid) REFERENCES udcparts(partrunid) ON DELETE CASCADE,
    CONSTRAINT fk_udcevents_session FOREIGN KEY (sessionid) REFERENCES udcsessions(sessionid) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Manual requests table - tracks timing of manual data entry
CREATE TABLE IF NOT EXISTS udcmanualrequests (
    requestid INT AUTO_INCREMENT PRIMARY KEY,
    partrunid INT,
    measurementid INT,
    requesttime DATETIME,
    responsetime DATETIME,
    responseseconds INT,
    description VARCHAR(255),
    INDEX idx_partrunid (partrunid),
    INDEX idx_requesttime (requesttime),
    INDEX idx_responseseconds (responseseconds),
    CONSTRAINT fk_udcmanualrequests_partrun FOREIGN KEY (partrunid) REFERENCES udcparts(partrunid) ON DELETE CASCADE,
    CONSTRAINT fk_udcmanualrequests_measurement FOREIGN KEY (measurementid) REFERENCES udcmeasurements(measurementid) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tool data table - TOOLDATA entries (tool offsets, wear, corner radius)
CREATE TABLE IF NOT EXISTS udctooldata (
    tooldataid INT AUTO_INCREMENT PRIMARY KEY,
    partrunid INT,
    sessionid INT NOT NULL,
    eventtime DATETIME,
    method VARCHAR(20),
    dimid VARCHAR(20),
    description VARCHAR(255),
    toolnumber INT,
    minval DECIMAL(12,6),
    maxval DECIMAL(12,6),
    actualval DECIMAL(12,6),
    deviation DECIMAL(12,6),
    oot TINYINT DEFAULT 0,
    INDEX idx_partrunid (partrunid),
    INDEX idx_sessionid (sessionid),
    INDEX idx_eventtime (eventtime),
    INDEX idx_toolnumber (toolnumber),
    INDEX idx_oot (oot),
    CONSTRAINT fk_udctooldata_partrun FOREIGN KEY (partrunid) REFERENCES udcparts(partrunid) ON DELETE SET NULL,
    CONSTRAINT fk_udctooldata_session FOREIGN KEY (sessionid) REFERENCES udcsessions(sessionid) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Errors table - error events from UDC logs
CREATE TABLE IF NOT EXISTS udcerrors (
    errorid INT AUTO_INCREMENT PRIMARY KEY,
    sessionid INT,
    machinenumber VARCHAR(20),
    eventtime DATETIME,
    errortype VARCHAR(100),
    errormessage TEXT,
    sourcemethod VARCHAR(255),
    INDEX idx_sessionid (sessionid),
    INDEX idx_machinenumber (machinenumber),
    INDEX idx_eventtime (eventtime),
    INDEX idx_errortype (errortype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Connections table - serial/network connection events
CREATE TABLE IF NOT EXISTS udcconnections (
    connectionid INT AUTO_INCREMENT PRIMARY KEY,
    sessionid INT,
    machinenumber VARCHAR(20),
    eventtime DATETIME,
    eventtype VARCHAR(20),
    comport VARCHAR(20),
    details VARCHAR(255),
    INDEX idx_sessionid (sessionid),
    INDEX idx_machinenumber (machinenumber),
    INDEX idx_eventtime (eventtime),
    INDEX idx_eventtype (eventtype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Active sessions table - tracks currently running machines
CREATE TABLE IF NOT EXISTS udcactivesessions (
    machinenumber VARCHAR(20) NOT NULL PRIMARY KEY,
    filepath VARCHAR(255),
    partnumber VARCHAR(50),
    badgenumber VARCHAR(20),
    partsrun INT DEFAULT 0,
    lastupdate DATETIME,
    sessionstart DATETIME,
    lastseen DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_lastupdate (lastupdate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Header updates table - badge changes during part runs
CREATE TABLE IF NOT EXISTS udcheaderupdates (
    updateid INT AUTO_INCREMENT PRIMARY KEY,
    partrunid INT,
    sessionid INT,
    machinenumber VARCHAR(20),
    eventtime DATETIME,
    details VARCHAR(255),
    description VARCHAR(255),
    badgenumber VARCHAR(20),
    INDEX idx_partrunid (partrunid),
    INDEX idx_sessionid (sessionid),
    INDEX idx_machinenumber (machinenumber),
    INDEX idx_eventtime (eventtime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Violations table - item crossing value changes
CREATE TABLE IF NOT EXISTS udcviolations (
    violationid INT AUTO_INCREMENT PRIMARY KEY,
    partrunid INT,
    sessionid INT,
    machinenumber VARCHAR(20),
    eventtime DATETIME,
    previousval DECIMAL(12,4),
    currentval DECIMAL(12,4),
    badgenumber VARCHAR(20),
    itemno VARCHAR(20),
    crossingdesc VARCHAR(255),
    INDEX idx_partrunid (partrunid),
    INDEX idx_sessionid (sessionid),
    INDEX idx_machinenumber (machinenumber),
    INDEX idx_eventtime (eventtime)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- CLM file tracking table - tracks imported JSON files (for incremental imports)
CREATE TABLE IF NOT EXISTS udcclmfiles (
    fileid INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(180) NOT NULL,
    machinenumber VARCHAR(10),
    filehash VARCHAR(32),
    filemodtime DATETIME,
    partrunid INT,
    sessionid INT,
    recordcount INT DEFAULT 0,
    importdate DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX uk_filename (filename),
    UNIQUE INDEX uk_filehash (filehash),
    INDEX idx_machine (machinenumber),
    INDEX idx_importdate (importdate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================================
-- Useful Views for Reporting
-- ============================================================================

-- Part runs with machine info
CREATE OR REPLACE VIEW vwudcpartruns AS
SELECT
    p.partrunid,
    p.sessionid,
    p.machineid,
    s.machinenumber,
    p.partnumber,
    p.opernumber,
    p.serialnumber,
    p.programname,
    p.jobnumber,
    p.badgenumber,
    p.programstart,
    p.programend,
    p.cycletime,
    p.changeover,
    p.measurementcount,
    p.manualcount,
    p.probecount,
    p.ootcount
FROM udcparts p
JOIN udcsessions s ON p.sessionid = s.sessionid;

-- Operator stats aggregation
CREATE OR REPLACE VIEW vwudcoperatorstats AS
SELECT
    badgenumber,
    COUNT(*) AS partsrun,
    AVG(cycletime) AS avgcycletime,
    AVG(changeover) AS avgchangeover,
    SUM(measurementcount) AS totalmeasurements,
    SUM(manualcount) AS totalmanual,
    SUM(ootcount) AS totaloot,
    MIN(programstart) AS firstrun,
    MAX(programend) AS lastrun
FROM udcparts
WHERE badgenumber IS NOT NULL AND badgenumber != ''
GROUP BY badgenumber;

-- Machine stats aggregation
CREATE OR REPLACE VIEW vwudcmachinestats AS
SELECT
    s.machinenumber,
    p.machineid,
    COUNT(*) AS partsrun,
    AVG(p.cycletime) AS avgcycletime,
    AVG(p.changeover) AS avgchangeover,
    SUM(p.measurementcount) AS totalmeasurements,
    SUM(p.ootcount) AS totaloot,
    MIN(p.programstart) AS firstrun,
    MAX(p.programend) AS lastrun
FROM udcparts p
JOIN udcsessions s ON p.sessionid = s.sessionid
GROUP BY s.machinenumber, p.machineid;

-- Manual request response times
CREATE OR REPLACE VIEW vwudcmanualtiming AS
SELECT
    r.requestid,
    p.badgenumber,
    s.machinenumber,
    r.requesttime,
    r.responsetime,
    r.responseseconds,
    r.description
FROM udcmanualrequests r
JOIN udcparts p ON r.partrunid = p.partrunid
JOIN udcsessions s ON p.sessionid = s.sessionid;

-- Tool health stats by machine
CREATE OR REPLACE VIEW vwudctoolhealthbymachine AS
SELECT
    s.machinenumber,
    COUNT(DISTINCT t.toolnumber) AS unique_tools,
    COUNT(*) AS total_measurements,
    SUM(t.oot) AS oot_count,
    ROUND(AVG(t.deviation), 6) AS avg_deviation,
    ROUND(MAX(ABS(t.deviation)), 6) AS max_deviation,
    MIN(t.eventtime) AS first_measurement,
    MAX(t.eventtime) AS last_measurement
FROM udctooldata t
JOIN udcsessions s ON t.sessionid = s.sessionid
GROUP BY s.machinenumber;

-- Tool health stats by tool number (across all machines)
CREATE OR REPLACE VIEW vwudctoolhealthbytool AS
SELECT
    s.machinenumber,
    t.toolnumber,
    t.description,
    COUNT(*) AS measurements,
    SUM(t.oot) AS oot_count,
    ROUND(AVG(t.deviation), 6) AS avg_deviation,
    ROUND(MAX(ABS(t.deviation)), 6) AS max_deviation,
    MAX(t.eventtime) AS last_measured
FROM udctooldata t
JOIN udcsessions s ON t.sessionid = s.sessionid
GROUP BY s.machinenumber, t.toolnumber, t.description;

-- Out of tolerance measurements detail
CREATE OR REPLACE VIEW vwudcootdetail AS
SELECT
    m.measurementid,
    s.machinenumber,
    p.partnumber,
    p.serialnumber,
    p.badgenumber,
    m.eventtime,
    m.method,
    m.dimid,
    m.description,
    m.minval,
    m.maxval,
    m.actualval,
    m.deviation
FROM udcmeasurements m
JOIN udcsessions s ON m.sessionid = s.sessionid
LEFT JOIN udcparts p ON m.partrunid = p.partrunid
WHERE m.oot = 1;

-- Tool OOT detail
CREATE OR REPLACE VIEW vwudctoolootdetail AS
SELECT
    t.tooldataid,
    s.machinenumber,
    p.partnumber,
    p.badgenumber,
    t.eventtime,
    t.toolnumber,
    t.description,
    t.minval,
    t.maxval,
    t.actualval,
    t.deviation
FROM udctooldata t
JOIN udcsessions s ON t.sessionid = s.sessionid
LEFT JOIN udcparts p ON t.partrunid = p.partrunid
WHERE t.oot = 1;

-- Active sessions with machine details
CREATE OR REPLACE VIEW vwudcactivesessions AS
SELECT
    a.machinenumber,
    m.hostname,
    m.alias,
    bu.businessunit,
    a.partnumber,
    a.badgenumber,
    a.partsrun,
    a.sessionstart,
    a.lastupdate,
    a.lastseen,
    TIMESTAMPDIFF(MINUTE, a.sessionstart, NOW()) AS runtime_minutes
FROM udcactivesessions a
LEFT JOIN machines m ON m.machinenumber = a.machinenumber
LEFT JOIN businessunits bu ON m.businessunitid = bu.businessunitid;

-- Violations with context
CREATE OR REPLACE VIEW vwudcviolations AS
SELECT
    v.violationid,
    v.machinenumber,
    v.eventtime,
    v.badgenumber,
    v.itemno,
    v.crossingdesc,
    v.previousval,
    v.currentval,
    p.partnumber,
    p.serialnumber
FROM udcviolations v
LEFT JOIN udcparts p ON v.partrunid = p.partrunid;

-- Daily production summary
CREATE OR REPLACE VIEW vwudcdailyproduction AS
SELECT
    DATE(p.programstart) AS production_date,
    s.machinenumber,
    COUNT(*) AS parts_produced,
    SUM(p.ootcount) AS total_oot,
    ROUND(SUM(p.ootcount) * 100.0 / COUNT(*), 2) AS oot_rate,
    ROUND(AVG(p.cycletime) / 60, 1) AS avg_cycle_minutes,
    ROUND(AVG(p.changeover) / 60, 1) AS avg_changeover_minutes,
    ROUND(SUM(p.cycletime) / 3600, 1) AS total_runtime_hours,
    COUNT(DISTINCT p.badgenumber) AS unique_operators
FROM udcparts p
JOIN udcsessions s ON p.sessionid = s.sessionid
WHERE p.programstart IS NOT NULL
GROUP BY DATE(p.programstart), s.machinenumber;

-- Error summary by machine
CREATE OR REPLACE VIEW vwudcerrorsummary AS
SELECT
    e.machinenumber,
    e.errortype,
    COUNT(*) AS error_count,
    MIN(e.eventtime) AS first_occurrence,
    MAX(e.eventtime) AS last_occurrence
FROM udcerrors e
GROUP BY e.machinenumber, e.errortype;

-- Connection events summary
CREATE OR REPLACE VIEW vwudcconnectionsummary AS
SELECT
    c.machinenumber,
    c.eventtype,
    c.comport,
    COUNT(*) AS event_count,
    MAX(c.eventtime) AS last_event
FROM udcconnections c
GROUP BY c.machinenumber, c.eventtype, c.comport;

-- Header updates (badge changes) with context
CREATE OR REPLACE VIEW vwudcheaderupdates AS
SELECT
    h.updateid,
    h.machinenumber,
    h.eventtime,
    h.badgenumber,
    h.description,
    h.details,
    p.partnumber,
    p.serialnumber
FROM udcheaderupdates h
LEFT JOIN udcparts p ON h.partrunid = p.partrunid;

-- CLM file import status
CREATE OR REPLACE VIEW vwudcclmfiles AS
SELECT
    f.fileid,
    f.machinenumber,
    f.filename,
    f.filehash,
    f.filemodtime,
    f.recordcount,
    f.importdate,
    p.partnumber,
    p.serialnumber
FROM udcclmfiles f
LEFT JOIN udcparts p ON f.partrunid = p.partrunid;

-- ============================================================================
-- Verify
-- ============================================================================
SELECT 'UDC tables created successfully' AS status;
SHOW TABLES LIKE 'udc%';