
CREATE TABLE CertificateTypes (
    CertificateTypeID VARCHAR(36) NOT NULL,
    CertificateTypeName VARCHAR(150) NOT NULL,
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (CertificateTypeID),
    UNIQUE KEY idx_certificate_types_name (CertificateTypeName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE WorkerCertificates (
    WorkerCertificateID VARCHAR(36) NOT NULL,
    WorkerID VARCHAR(36) NOT NULL,
    CertificateTypeID VARCHAR(36) NOT NULL,
    CertificateNumber VARCHAR(150),
    ExpiryDate VARCHAR(30),
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (WorkerCertificateID),
    KEY idx_worker_certificates_worker (WorkerID),
    KEY idx_worker_certificates_type (CertificateTypeID),
    CONSTRAINT fk_worker_certificates_worker FOREIGN KEY (WorkerID) REFERENCES Workers (WorkerID),
    CONSTRAINT fk_worker_certificates_type FOREIGN KEY (CertificateTypeID) REFERENCES CertificateTypes (CertificateTypeID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Sites (
    SiteID VARCHAR(36) NOT NULL,
    SiteName VARCHAR(255) NOT NULL,
    Address TEXT,
    Latitude DOUBLE,
    Longitude DOUBLE,
    AllowedRadius DOUBLE NOT NULL DEFAULT 150,
    WorkStartTime VARCHAR(20),
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (SiteID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE WorkEntries (
    WorkEntryID VARCHAR(36) NOT NULL,
    WorkerID VARCHAR(36) NOT NULL,
    SiteID VARCHAR(36) NOT NULL,
    WorkDate VARCHAR(30) NOT NULL,
    LocationText TEXT,
    Comments TEXT,
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (WorkEntryID),
    KEY idx_work_entries_worker (WorkerID),
    KEY idx_work_entries_site (SiteID),
    KEY idx_work_entries_date (WorkDate),
    CONSTRAINT fk_work_entries_worker FOREIGN KEY (WorkerID) REFERENCES Workers (WorkerID),
    CONSTRAINT fk_work_entries_site FOREIGN KEY (SiteID) REFERENCES Sites (SiteID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE AttendanceEvents (
    AttendanceID VARCHAR(36) NOT NULL,
    WorkerID VARCHAR(36) NOT NULL,
    SiteID VARCHAR(36) NOT NULL,
    EventType VARCHAR(50) NOT NULL,
    EventTime VARCHAR(40) NOT NULL,
    WorkingHours INT,
    Latitude DOUBLE,
    Longitude DOUBLE,
    Accuracy DOUBLE,
    DistanceFromSite DOUBLE,
    InsideAllowedRadius TINYINT,
    EmployeeNote TEXT,
    CreatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (AttendanceID),
    KEY idx_attendance_worker (WorkerID),
    KEY idx_attendance_site (SiteID),
    KEY idx_attendance_time (EventTime),
    CONSTRAINT fk_attendance_worker FOREIGN KEY (WorkerID) REFERENCES Workers (WorkerID),
    CONSTRAINT fk_attendance_site FOREIGN KEY (SiteID) REFERENCES Sites (SiteID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE WorkCategories (
    CategoryID VARCHAR(36) NOT NULL,
    CategoryName VARCHAR(150) NOT NULL,
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (CategoryID),
    KEY idx_work_categories_active (IsActive)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Materials (
    MaterialID VARCHAR(36) NOT NULL,
    MaterialName VARCHAR(150) NOT NULL,
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (MaterialID),
    KEY idx_materials_active (IsActive)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE CategoryMaterials (
    CategoryMaterialID VARCHAR(36) NOT NULL,
    CategoryID VARCHAR(36) NOT NULL,
    MaterialID VARCHAR(36) NOT NULL,
    MeasurementProfile VARCHAR(100),
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (CategoryMaterialID),
    UNIQUE KEY idx_category_material_unique (CategoryID, MaterialID),
    KEY idx_category_material_category (CategoryID),
    KEY idx_category_material_material (MaterialID),
    CONSTRAINT fk_category_material_category FOREIGN KEY (CategoryID) REFERENCES WorkCategories (CategoryID),
    CONSTRAINT fk_category_material_material FOREIGN KEY (MaterialID) REFERENCES Materials (MaterialID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE MeasuredItems (
    MeasuredItemID VARCHAR(36) NOT NULL,
    CategoryID VARCHAR(36) NOT NULL,
    ItemName VARCHAR(150) NOT NULL,
    MeasurementType VARCHAR(50) NOT NULL,
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (MeasuredItemID),
    KEY idx_measured_items_category (CategoryID),
    KEY idx_measured_items_active (IsActive),
    CONSTRAINT fk_measured_items_category FOREIGN KEY (CategoryID) REFERENCES WorkCategories (CategoryID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE PipeDiameters (
    PipeDiameterID VARCHAR(36) NOT NULL,
    DiameterMM DOUBLE NOT NULL,
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (PipeDiameterID),
    UNIQUE KEY idx_pipe_diameters_value (DiameterMM),
    KEY idx_pipe_diameters_active (IsActive)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE CompletedWork (
    CompletedWorkID VARCHAR(36) NOT NULL,
    WorkerID VARCHAR(36) NOT NULL,
    SiteID VARCHAR(36) NOT NULL,
    WorkDate VARCHAR(30) NOT NULL,
    LocationText TEXT NOT NULL,
    Service VARCHAR(255) NOT NULL,
    Material VARCHAR(255) NOT NULL,
    CategoryID VARCHAR(36),
    MaterialID VARCHAR(36),
    MeasuredItemID VARCHAR(36),
    MeasuredItem VARCHAR(150),
    MeasurementType VARCHAR(50),
    PipeDiameter DOUBLE,
    InsulationThickness DOUBLE,
    CircumferenceInches DOUBLE,
    WorkType VARCHAR(100),
    WorkDurationMinutes INT,
    Quantity DOUBLE NOT NULL,
    Unit VARCHAR(50) NOT NULL,
    Comments TEXT,
    CreatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (CompletedWorkID),
    KEY idx_completed_work_worker (WorkerID),
    KEY idx_completed_work_site (SiteID),
    KEY idx_completed_work_date (WorkDate),
    CONSTRAINT fk_completed_work_worker FOREIGN KEY (WorkerID) REFERENCES Workers (WorkerID),
    CONSTRAINT fk_completed_work_site FOREIGN KEY (SiteID) REFERENCES Sites (SiteID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE CompletedWorkItems (
    CompletedWorkItemID VARCHAR(36) NOT NULL,
    CompletedWorkID VARCHAR(36) NOT NULL,
    MeasuredItemID VARCHAR(36) NOT NULL,
    MeasuredItemName VARCHAR(150) NOT NULL,
    Quantity DOUBLE NOT NULL,
    Unit VARCHAR(50) NOT NULL,
    Comments TEXT,
    IsActive TINYINT NOT NULL DEFAULT 1,
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (CompletedWorkItemID),
    KEY idx_completed_work_items_work (CompletedWorkID),
    KEY idx_completed_work_items_measured (MeasuredItemID),
    KEY idx_completed_work_items_active (IsActive),
    CONSTRAINT fk_completed_work_items_work FOREIGN KEY (CompletedWorkID) REFERENCES CompletedWork (CompletedWorkID),
    CONSTRAINT fk_completed_work_items_measured FOREIGN KEY (MeasuredItemID) REFERENCES MeasuredItems (MeasuredItemID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE AppLogs (
    LogID VARCHAR(36) NOT NULL,
    Timestamp VARCHAR(40) NOT NULL,
    Level VARCHAR(50) NOT NULL,
    ErrorCode VARCHAR(100),
    Source VARCHAR(150) NOT NULL,
    Action VARCHAR(150) NOT NULL,
    Message TEXT NOT NULL,
    Details LONGTEXT,
    StackTrace LONGTEXT,
    WorkerID VARCHAR(36),
    PRIMARY KEY (LogID),
    KEY idx_app_logs_timestamp (Timestamp),
    KEY idx_app_logs_level (Level),
    KEY idx_app_logs_error_code (ErrorCode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE AuditLogs (
    AuditID VARCHAR(36) NOT NULL,
    Timestamp VARCHAR(40) NOT NULL,
    WorkerID VARCHAR(36),
    Action VARCHAR(150) NOT NULL,
    EntityType VARCHAR(100) NOT NULL,
    EntityID VARCHAR(36),
    OldValue LONGTEXT,
    NewValue LONGTEXT,
    Details LONGTEXT,
    PRIMARY KEY (AuditID),
    KEY idx_audit_logs_timestamp (Timestamp),
    KEY idx_audit_logs_worker (WorkerID),
    KEY idx_audit_logs_entity (EntityType, EntityID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE AppSettings (
    SettingsID VARCHAR(50) NOT NULL,
    WorkDayAlertEnabled TINYINT NOT NULL DEFAULT 1,
    WorkMode VARCHAR(50) NOT NULL DEFAULT 'Daywork',
    DetailedWorkRegistrationEnabled TINYINT NOT NULL DEFAULT 1,
    ReportEmailAddresses TEXT,
    OfficeEmailAddress VARCHAR(255),
    ReportControlEmailAddress VARCHAR(255),
    SendCopyToSelf TINYINT NOT NULL DEFAULT 1,
    AutomaticSyncEnabled TINYINT NOT NULL DEFAULT 0,
    HelpIconEnabled TINYINT NOT NULL DEFAULT 1,
    UiLanguage VARCHAR(10) NOT NULL DEFAULT 'en',
    TranslationIconEnabled TINYINT NOT NULL DEFAULT 1,
    SecondaryLanguage VARCHAR(10) NOT NULL DEFAULT 'pl',
    CreatedAt VARCHAR(40) NOT NULL,
    UpdatedAt VARCHAR(40) NOT NULL,
    PRIMARY KEY (SettingsID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET @now = DATE_FORMAT(NOW(6), '%Y-%m-%dT%H:%i:%s.%f');

INSERT IGNORE INTO CertificateTypes (CertificateTypeID, CertificateTypeName, IsActive, CreatedAt, UpdatedAt) VALUES
('a1111111-1111-4111-8111-111111111111','Asbestos Awareness',1,@now,@now),
('a2222222-2222-4222-8222-222222222222','Manual Handling',1,@now,@now),
('a3333333-3333-4333-8333-333333333333','Working at Height',1,@now,@now),
('a4444444-4444-4444-8444-444444444444','IPAF Licence',1,@now,@now),
('a5555555-5555-4555-8555-555555555555','PASMA Licence',1,@now,@now);

INSERT IGNORE INTO WorkCategories (CategoryID, CategoryName, IsActive, CreatedAt, UpdatedAt) VALUES
('11111111-1111-4111-8111-111111111111','Pipework',1,@now,@now),
('22222222-2222-4222-8222-222222222222','Ductwork',1,@now,@now);

INSERT IGNORE INTO Materials (MaterialID, MaterialName, IsActive, CreatedAt, UpdatedAt) VALUES
('31111111-1111-4111-8111-111111111111','Pipe Section',1,@now,@now),
('32222222-2222-4222-8222-222222222222','Wool',1,@now,@now),
('33333333-3333-4333-8333-333333333333','Muftilag',1,@now,@now),
('34444444-4444-4444-8444-444444444444','PIB',1,@now,@now),
('35555555-5555-4555-8555-555555555555','Proclad',1,@now,@now),
('36666666-6666-4666-8666-666666666666','Metal Cladding',1,@now,@now),
('37777777-7777-4777-8777-777777777777','Wool Slabs',1,@now,@now),
('38888888-8888-4888-8888-888888888888','Phenolic Board',1,@now,@now),
('39999999-9999-4999-8999-999999999999','Lamela',1,@now,@now),
('3aaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa','Silver Foil',1,@now,@now);

INSERT IGNORE INTO CategoryMaterials (CategoryMaterialID, CategoryID, MaterialID, MeasurementProfile, IsActive, CreatedAt, UpdatedAt) VALUES
('40000001-0000-4000-8000-000000000001','11111111-1111-4111-8111-111111111111','31111111-1111-4111-8111-111111111111','PIPE_SECTION',1,@now,@now),
('40000002-0000-4000-8000-000000000002','11111111-1111-4111-8111-111111111111','32222222-2222-4222-8222-222222222222','AREA_WITH_THICKNESS',1,@now,@now),
('40000003-0000-4000-8000-000000000003','11111111-1111-4111-8111-111111111111','33333333-3333-4333-8333-333333333333','LINEAR',1,@now,@now),
('40000004-0000-4000-8000-000000000004','11111111-1111-4111-8111-111111111111','34444444-4444-4444-8444-444444444444','LINEAR',1,@now,@now),
('40000005-0000-4000-8000-000000000005','11111111-1111-4111-8111-111111111111','35555555-5555-4555-8555-555555555555','LINEAR',1,@now,@now),
('40000006-0000-4000-8000-000000000006','11111111-1111-4111-8111-111111111111','36666666-6666-4666-8666-666666666666','METAL_CLADDING',1,@now,@now),
('40000007-0000-4000-8000-000000000007','11111111-1111-4111-8111-111111111111','39999999-9999-4999-8999-999999999999','AREA_WITH_THICKNESS',1,@now,@now),
('40000008-0000-4000-8000-000000000008','11111111-1111-4111-8111-111111111111','3aaaaaaa-aaaa-4aaa-8aaa-aaaaaaaaaaaa','LINEAR',1,@now,@now),
('40000009-0000-4000-8000-000000000009','22222222-2222-4222-8222-222222222222','32222222-2222-4222-8222-222222222222','AREA_WITH_THICKNESS',1,@now,@now),
('40000010-0000-4000-8000-000000000010','22222222-2222-4222-8222-222222222222','37777777-7777-4777-8777-777777777777','AREA_WITH_THICKNESS',1,@now,@now),
('40000011-0000-4000-8000-000000000011','22222222-2222-4222-8222-222222222222','38888888-8888-4888-8888-888888888888','AREA_WITH_THICKNESS',1,@now,@now),
('40000012-0000-4000-8000-000000000012','22222222-2222-4222-8222-222222222222','35555555-5555-4555-8555-555555555555','AREA',1,@now,@now),
('40000013-0000-4000-8000-000000000013','22222222-2222-4222-8222-222222222222','36666666-6666-4666-8666-666666666666','AREA',1,@now,@now);

INSERT IGNORE INTO MeasuredItems (MeasuredItemID, CategoryID, ItemName, MeasurementType, IsActive, CreatedAt, UpdatedAt) VALUES
('52222222-2222-4222-8222-222222222222','11111111-1111-4111-8111-111111111111','Valves','EACH',1,@now,@now),
('53333333-3333-4333-8333-333333333333','11111111-1111-4111-8111-111111111111','Flanges','EACH',1,@now,@now),
('54444444-4444-4444-8444-444444444444','11111111-1111-4111-8111-111111111111','Bends','EACH',1,@now,@now),
('55555555-5555-4555-8555-555555555555','11111111-1111-4111-8111-111111111111','Pumps','EACH',1,@now,@now),
('56666666-6666-4666-8666-666666666666','11111111-1111-4111-8111-111111111111','Vessels','EACH',1,@now,@now),
('57777777-7777-4777-8777-777777777777','11111111-1111-4111-8111-111111111111','Plenum Boxes','EACH',1,@now,@now);

INSERT IGNORE INTO PipeDiameters (PipeDiameterID, DiameterMM, IsActive, CreatedAt, UpdatedAt) VALUES
('61111111-1111-4111-8111-111111111111',15.0,1,@now,@now),
('62222222-2222-4222-8222-222222222222',22.0,1,@now,@now),
('63333333-3333-4333-8333-333333333333',28.0,1,@now,@now),
('64444444-4444-4444-8444-444444444444',35.0,1,@now,@now),
('65555555-5555-4555-8555-555555555555',42.0,1,@now,@now),
('66666666-6666-4666-8666-666666666666',48.0,1,@now,@now),
('67777777-7777-4777-8777-777777777777',54.0,1,@now,@now),
('68888888-8888-4888-8888-888888888888',60.0,1,@now,@now);

INSERT IGNORE INTO AppSettings (SettingsID, WorkDayAlertEnabled, WorkMode, DetailedWorkRegistrationEnabled, ReportEmailAddresses, OfficeEmailAddress, ReportControlEmailAddress, SendCopyToSelf, AutomaticSyncEnabled, HelpIconEnabled, UiLanguage, TranslationIconEnabled, SecondaryLanguage, CreatedAt, UpdatedAt)
VALUES ('APP_SETTINGS',1,'Daywork',1,'','','',1,0,1,'en',1,'pl',@now,@now);

