create_schema.sql

-- =============================================
-- MES Schema - MS SQL Server 2012 Compatible
-- Tables + Sample Data (No Foreign Keys)
-- =============================================

-- =============================================
-- LOOKUP / TYPE TABLES
-- =============================================

CREATE TABLE operation_type (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    name VARCHAR(50) NOT NULL,
    sort INT NOT NULL,
    CONSTRAINT PK_operation_type PRIMARY KEY (id)
);

INSERT INTO operation_type (name, sort) VALUES
('init', 1),
('progress', 2),
('depanel', 3),
('test', 4),
('sna', 5),
('complete', 6),
('repair', 7);


CREATE TABLE operation (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    type_id UNIQUEIDENTIFIER NULL,
    name VARCHAR(50) NOT NULL,
    is_out_of_route BIT NOT NULL DEFAULT 0,
    sort INT NOT NULL DEFAULT 0,
	requires_signoff BIT NOT NULL DEFAULT 0,
    requires_first_off BIT NOT NULL DEFAULT 0,
    auto_complete BIT NOT NULL DEFAULT 0,
    is_start BIT NOT NULL DEFAULT 0,
    is_end BIT NOT NULL DEFAULT 0,
    CONSTRAINT PK_operation PRIMARY KEY (id)
);

INSERT INTO operation (type_id, name) VALUES
((SELECT id FROM operation_type WHERE name = 'init'),      'release'),
((SELECT id FROM operation_type WHERE name = 'progress'),  'SMT'),
((SELECT id FROM operation_type WHERE name = 'progress'),  'pushline'),
((SELECT id FROM operation_type WHERE name = 'progress'),  'wave'),
((SELECT id FROM operation_type WHERE name = 'test'),      'ict'),
((SELECT id FROM operation_type WHERE name = 'sna'),       'sna'),
((SELECT id FROM operation_type WHERE name = 'test'),      'func'),
((SELECT id FROM operation_type WHERE name = 'complete'),  'stock'),
((SELECT id FROM operation_type WHERE name = 'repair'),    'repair');


CREATE TABLE defect_type (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    name VARCHAR(50) NOT NULL,
    sort INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_defect_type PRIMARY KEY (id)
);

INSERT INTO defect_type (name) VALUES
('board'),
('component'),
('placement'),
('termination');


CREATE TABLE defect (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    type_id UNIQUEIDENTIFIER NULL,
    name VARCHAR(50) NOT NULL,
    is_active BIT NOT NULL DEFAULT 1,
    sort INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_defect PRIMARY KEY (id)
);

INSERT INTO defect (type_id, name) VALUES
((SELECT id FROM defect_type WHERE name = 'termination'), 'bridging'),
((SELECT id FROM defect_type WHERE name = 'placement'),   'missing'),
((SELECT id FROM defect_type WHERE name = 'placement'),   'tombstone'),
((SELECT id FROM defect_type WHERE name = 'placement'),   'polarity'),
((SELECT id FROM defect_type WHERE name = 'termination'), 'insufficient solder'),
((SELECT id FROM defect_type WHERE name = 'termination'), 'excess solder'),
((SELECT id FROM defect_type WHERE name = 'component'),   'damaged'),
((SELECT id FROM defect_type WHERE name = 'board'),       'contamination'),
((SELECT id FROM defect_type WHERE name = 'placement'),   'misaligned'),
((SELECT id FROM defect_type WHERE name = 'component'),   'wrong part');


CREATE TABLE sna_type (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    name VARCHAR(50) NOT NULL,
    validation_url VARCHAR(MAX) NULL,
    sort INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_sna_type PRIMARY KEY (id)
);

INSERT INTO sna_type (name) VALUES
('assembly'),
('regex'),
('barcode');


CREATE TABLE container_type (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    name VARCHAR(50) NOT NULL,
    sort INT NOT NULL DEFAULT 0,
    CONSTRAINT PK_container_type PRIMARY KEY (id)
);

INSERT INTO container_type (name) VALUES
('panel'),
('rack'),
('box'),
('cart'),
('shelf'),
('workstation'),
('room'),
('facility');


-- =============================================
-- ROUTE TABLES
-- =============================================

CREATE TABLE route (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    name VARCHAR(50) NOT NULL,
    is_active BIT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_route PRIMARY KEY (id)
);

CREATE TABLE route_step (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    route_id UNIQUEIDENTIFIER NULL,
    operation_id UNIQUEIDENTIFIER NULL,
    sort INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_route_step PRIMARY KEY (id)
);


-- =============================================
-- SNA TABLES
-- =============================================

CREATE TABLE sna (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    partnumber VARCHAR(30) NOT NULL,
    revision VARCHAR(10) NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_sna PRIMARY KEY (id)
);

CREATE TABLE sna_definition (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    sna_id UNIQUEIDENTIFIER NULL,
    step_id UNIQUEIDENTIFIER NULL,
    type_id UNIQUEIDENTIFIER NULL,
    requirement varchar(50) NOT NULL,
	instructions varchar(max) null,
    is_active BIT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_sna_definition PRIMARY KEY (id)
);


-- =============================================
-- JOB / WIP TABLES
-- =============================================

CREATE TABLE job (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    route_id UNIQUEIDENTIFIER NOT NULL,
    sna_id UNIQUEIDENTIFIER NULL,
    workorder VARCHAR(20) NOT NULL,
    partnumber VARCHAR(30) NOT NULL,
    revision VARCHAR(10) NOT NULL,
    quantity int NOT NULL,
    panel_xy VARCHAR(5) NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_job PRIMARY KEY (id),
    CONSTRAINT UQ_job_workorder UNIQUE (workorder)
);

CREATE TABLE container (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    parent_id UNIQUEIDENTIFIER NULL,
    type_id UNIQUEIDENTIFIER NOT NULL,
    license_plate VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_container PRIMARY KEY (id)
);

CREATE TABLE wip (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    job_id UNIQUEIDENTIFIER NOT NULL,
    container_id UNIQUEIDENTIFIER NULL,
    serial VARCHAR(20) NOT NULL,
    panel_address VARCHAR(5) NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_wip PRIMARY KEY (id),
    CONSTRAINT UQ_wip_serial UNIQUE (serial)
);

CREATE TABLE wip_progress (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    wip_id UNIQUEIDENTIFIER NOT NULL,
    step_id UNIQUEIDENTIFIER NOT NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    completed_at DATETIME NULL,
    completed_by VARCHAR(50) NULL,
    CONSTRAINT PK_wip_progress PRIMARY KEY (id)
);

CREATE TABLE wip_progress_signoff (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    progress_id UNIQUEIDENTIFIER NOT NULL,
    badge VARCHAR(50) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    CONSTRAINT PK_wip_progress_signoff PRIMARY KEY (id)
);

CREATE TABLE wip_first_off (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    progress_id UNIQUEIDENTIFIER NOT NULL,
    inspection_type VARCHAR(20) NOT NULL,
    status VARCHAR(10) NOT NULL,
    area VARCHAR(50) NULL,
    side VARCHAR(10) NULL,
    bga_qfn_locations NVARCHAR(MAX) NULL,
    comments NVARCHAR(MAX) NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_wip_first_off PRIMARY KEY (id)
);

CREATE TABLE wip_first_off_images (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    first_off_id UNIQUEIDENTIFIER NOT NULL,
    filename VARCHAR(200) NOT NULL,
    original_filename VARCHAR(200) NOT NULL,
    file_size INT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    CONSTRAINT PK_wip_first_off_images PRIMARY KEY (id)
);

CREATE TABLE wip_test (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    progress_id UNIQUEIDENTIFIER NOT NULL,
    is_pass BIT NULL,
    notes VARCHAR(MAX) NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_wip_test PRIMARY KEY (id)
);


CREATE TABLE wip_defect (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    wip_id UNIQUEIDENTIFIER NOT NULL,
    defect_id UNIQUEIDENTIFIER NOT NULL,
    refdes VARCHAR(10) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    repaired_at DATETIME NULL,
    repaired_by VARCHAR(50) NULL,
    validated_at DATETIME NULL,
    validated_by VARCHAR(50) NULL,
    CONSTRAINT PK_wip_defect PRIMARY KEY (id)
);

CREATE TABLE wip_sna (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    progress_id UNIQUEIDENTIFIER NOT NULL,
    definition_id UNIQUEIDENTIFIER NOT NULL,
    value VARCHAR(50) NULL,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    divorced_at DATETIME NULL,
    divorced_by VARCHAR(50) NULL,
    CONSTRAINT PK_wip_sna PRIMARY KEY (id)
);

CREATE TABLE api_endpoint (                                                                                                                                                                                                                id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
    [key] VARCHAR(50) NOT NULL UNIQUE,                                                                                                                                                                                                   
    name VARCHAR(100) NOT NULL,
    url VARCHAR(500) NOT NULL,
    params NVARCHAR(MAX) NOT NULL,
    field_mapping NVARCHAR(MAX) NOT NULL,
    display_field VARCHAR(50) NOT NULL,
    sub_display_field VARCHAR(50) NULL,
    created_at DATETIME DEFAULT GETDATE(),
    created_by VARCHAR(100)
);

-- And optionally seed the two existing endpoints:

--INSERT INTO api_endpoint ([key], name, url, params, field_mapping, display_field, sub_display_field) VALUES
--('sna_part_number', 'SNA Part Number Lookup', 'http://erp.example.com/api/api.php',
--'{"endpoint":"mrp/table","tablename":"part_master","type_01":"A","prtnum_01":"{partnumber}%","limit":"10","orderby":"prtnum_01"}',
--'{"prtnum_01":"partnumber","revlev_01":"revision"}',
--'prtnum_01', 'revlev_01');

--INSERT INTO api_endpoint ([key], name, url, params, field_mapping, display_field, sub_display_field) VALUES
--('job_work_order', 'Job Work Order Lookup', 'http://erp.example.com/api/api.php',
--'{"endpoint":"mrp/table","tablename":"Order_Master","ordnum_10":"{workorder}%","prtnum_10":"{partnumber}%","limit":"10","orderby":"ordnum_10"}',
--'{"ordnum_10":"workorder","prtnum_10":"partnumber","revlev_10":"revision","curqty_10":"quantity"}',
--'ordnum_10', 'prtnum_10');


CREATE TABLE webhook (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    name VARCHAR(100) NOT NULL,
    url VARCHAR(500) NOT NULL,
    hook_type VARCHAR(10) NOT NULL,
    operation_id UNIQUEIDENTIFIER NULL,
    auth_headers NVARCHAR(MAX) NULL,
    timeout_ms INT NOT NULL DEFAULT 5000,
    is_active BIT NOT NULL DEFAULT 1,
    sort INT NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_webhook PRIMARY KEY (id)
);

CREATE TABLE webhook_log (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    webhook_id UNIQUEIDENTIFIER NOT NULL,
    hook_type VARCHAR(10) NOT NULL,
    serial VARCHAR(20) NOT NULL,
    operation_id UNIQUEIDENTIFIER NOT NULL,
    request_payload NVARCHAR(MAX) NULL,
    response_payload NVARCHAR(MAX) NULL,
    status_code INT NULL,
    duration_ms INT NULL,
    success BIT NOT NULL DEFAULT 0,
    error_message VARCHAR(500) NULL,
    created_at DATETIME DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_webhook_log PRIMARY KEY (id)
);


-- =============================================
-- NPI TABLES
-- =============================================

CREATE TABLE npi_form_field (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    label VARCHAR(100) NOT NULL,
    field_type VARCHAR(20) NOT NULL,
    is_required BIT NOT NULL DEFAULT 0,
    config VARCHAR(MAX) NULL,
    sort INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_npi_form_field PRIMARY KEY (id)
);

CREATE TABLE npi (
    id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    assembly VARCHAR(100) NOT NULL,
    revision VARCHAR(50) NOT NULL DEFAULT '',
    route_id UNIQUEIDENTIFIER NULL,
    sna_id UNIQUEIDENTIFIER NULL,
    form_data VARCHAR(MAX) NULL,
    is_active BIT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT GETDATE(),
    created_by VARCHAR(50) NULL,
    CONSTRAINT PK_npi PRIMARY KEY (id)
);

-- =============================================
-- LOCATION TRACKING TABLES
-- =============================================

CREATE TABLE container_move (
    id           UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    container_id UNIQUEIDENTIFIER NOT NULL,   -- panel being moved
    from_id      UNIQUEIDENTIFIER NULL,        -- previous location (null = first move)
    to_id        UNIQUEIDENTIFIER NULL,        -- destination (null = cleared/unknown)
    moved_at     DATETIME NOT NULL DEFAULT GETDATE(),
    moved_by     VARCHAR(50) NULL,
    CONSTRAINT PK_container_move PRIMARY KEY (id)
);
CREATE INDEX IX_container_move_container_moved ON container_move (container_id, moved_at DESC);

PRINT 'Schema and sample data created successfully.';