coi/db-init/other-table-...

/*
    Seed mock data for the main non-master application tables.
    This keeps inserts idempotent and avoids touching Django internal metadata.
*/

USE [coi_db];
GO

SET NOCOUNT ON;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.auth_user WHERE username = N'admin')
BEGIN
    INSERT INTO dbo.auth_user (
        password, last_login, is_superuser, username, first_name, last_name,
        email, is_staff, is_active, date_joined
    )
    VALUES (
        N'pbkdf2_sha256$600000$Xn2U8rTJlAmDG6TaLgb9WQ$ElMUGivveb2WqmckmheHTb8jaxVuu1NJ2/QAAnpvZ7w=',
        SYSUTCDATETIME(), 1, N'admin', N'System', N'Admin',
        N'admin@example.com', 1, 1, SYSUTCDATETIME()
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.auth_user WHERE username = N'qa_demo')
BEGIN
    INSERT INTO dbo.auth_user (
        password, last_login, is_superuser, username, first_name, last_name,
        email, is_staff, is_active, date_joined
    )
    VALUES (
        N'pbkdf2_sha256$600000$AungG757m60OAcrPdWKW9v$StWU6+PC32Oeneaoz69fl5h2fbIhrK4CLmx5+6uSjCc=',
        SYSUTCDATETIME(), 0, N'qa_demo', N'Quality', N'Engineer',
        N'qa_demo@example.com', 1, 1, SYSUTCDATETIME()
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.sysadmin_userprofile p JOIN dbo.auth_user u ON u.id = p.user_id WHERE u.username = N'admin')
BEGIN
    INSERT INTO dbo.sysadmin_userprofile (
        bio, profile_picture, signed_picture, email, position, user_id
    )
    SELECT
        N'Default administrator account for local development.',
        N'profile/2026/03/16/admin.png',
        N'signed/2026/03/16/admin-sign.png',
        N'admin@example.com',
        N'QA_MANAGER',
        u.id
    FROM dbo.auth_user u
    WHERE u.username = N'admin';
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.sysadmin_userprofile p JOIN dbo.auth_user u ON u.id = p.user_id WHERE u.username = N'qa_demo')
BEGIN
    INSERT INTO dbo.sysadmin_userprofile (
        bio, profile_picture, signed_picture, email, position, user_id
    )
    SELECT
        N'QA demo user for report and template testing.',
        N'profile/2026/03/16/qa-demo.png',
        N'signed/2026/03/16/qa-demo-sign.png',
        N'qa_demo@example.com',
        N'QA_ENGINEER',
        u.id
    FROM dbo.auth_user u
    WHERE u.username = N'qa_demo';
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.core_report WHERE name = N'Demo Daily Hardness Report')
BEGIN
    INSERT INTO dbo.core_report (
        name, created_at, updated_at, created_by_id, [file]
    )
    SELECT
        N'Demo Daily Hardness Report',
        SYSUTCDATETIME(),
        SYSUTCDATETIME(),
        u.id,
        N'reports/files/demo-daily-hardness-report.xlsx'
    FROM dbo.auth_user u
    WHERE u.username = N'admin';
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.core_report WHERE name = N'Demo Measurement Summary')
BEGIN
    INSERT INTO dbo.core_report (
        name, created_at, updated_at, created_by_id, [file]
    )
    SELECT
        N'Demo Measurement Summary',
        SYSUTCDATETIME(),
        SYSUTCDATETIME(),
        u.id,
        N'reports/files/demo-measurement-summary.xlsx'
    FROM dbo.auth_user u
    WHERE u.username = N'qa_demo';
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.core_customertemplatemapping WHERE customer_name = N'Yamato Industrial')
BEGIN
    INSERT INTO dbo.core_customertemplatemapping (
        customer_name, template_names, created_at, created_by_id, updated_at
    )
    SELECT
        N'Yamato Industrial',
        N'["dimension","hardness_out","centering"]',
        SYSUTCDATETIME(),
        u.id,
        SYSUTCDATETIME()
    FROM dbo.auth_user u
    WHERE u.username = N'admin';
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.core_customertemplatemapping WHERE customer_name = N'Tokyo Electric')
BEGIN
    INSERT INTO dbo.core_customertemplatemapping (
        customer_name, template_names, created_at, created_by_id, updated_at
    )
    SELECT
        N'Tokyo Electric',
        N'["dimension_app","hardness_out_in","thickness_8_point"]',
        SYSUTCDATETIME(),
        u.id,
        SYSUTCDATETIME()
    FROM dbo.auth_user u
    WHERE u.username = N'qa_demo';
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.core_productdrawing WHERE code_no = N'MG-CODE-01' AND lot_no = N'LOT-MG-001')
BEGIN
    INSERT INTO dbo.core_productdrawing (
        code_no, code_no_mks, lot_no, drawing, description, created_at, updated_at, created_by_id
    )
    SELECT
        N'MG-CODE-01',
        N'MKS-MG-9001',
        N'LOT-MG-001',
        N'drawings/mg-code-01.png',
        N'Mock drawing for MG product used in report previews.',
        SYSUTCDATETIME(),
        SYSUTCDATETIME(),
        u.id
    FROM dbo.auth_user u
    WHERE u.username = N'admin';
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.core_productdrawing WHERE code_no = N'V-CODE-01' AND lot_no = N'LOT-V-001')
BEGIN
    INSERT INTO dbo.core_productdrawing (
        code_no, code_no_mks, lot_no, drawing, description, created_at, updated_at, created_by_id
    )
    SELECT
        N'V-CODE-01',
        N'MKS-V-1001',
        N'LOT-V-001',
        N'drawings/v-code-01.png',
        N'Mock drawing for V product local testing.',
        SYSUTCDATETIME(),
        SYSUTCDATETIME(),
        u.id
    FROM dbo.auth_user u
    WHERE u.username = N'qa_demo';
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.core_mkscodemap WHERE mgt_code = N'MG-CODE-01')
BEGIN
    INSERT INTO dbo.core_mkscodemap (
        mgt_code, mks_code, created_at, updated_at
    )
    VALUES (
        N'MG-CODE-01',
        N'MKS-MG-9001',
        SYSUTCDATETIME(),
        SYSUTCDATETIME()
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.core_mkscodemap WHERE mgt_code = N'V-CODE-01')
BEGIN
    INSERT INTO dbo.core_mkscodemap (
        mgt_code, mks_code, created_at, updated_at
    )
    VALUES (
        N'V-CODE-01',
        N'MKS-V-1001',
        SYSUTCDATETIME(),
        SYSUTCDATETIME()
    );
END;
GO

IF NOT EXISTS (SELECT 1 FROM dbo.core_mkscodemap WHERE mgt_code = N'E-CODE-01')
BEGIN
    INSERT INTO dbo.core_mkscodemap (
        mgt_code, mks_code, created_at, updated_at
    )
    VALUES (
        N'E-CODE-01',
        N'MKS-E-7001',
        SYSUTCDATETIME(),
        SYSUTCDATETIME()
    );
END;
GO