-- Import script for Fusion Engineering Installation List
-- Created: 2024-12-10

-- Disable foreign key checks temporarily
SET FOREIGN_KEY_CHECKS=0;

-- Step 1: Import unique companies
INSERT IGNORE INTO contact_companies (company_name, created_at, updated_at)
SELECT DISTINCT 
    TRIM(SUBSTRING_INDEX(`Customer Name`, '(', 1)) as company_name,
    NOW() as created_at,
    NOW() as updated_at
FROM TEMPORARY_IMPORT_TABLE;

-- Step 2: Import contact locations
INSERT IGNORE INTO contact_contacts (
    company_id,
    location,
    unit_name,
    created_at,
    updated_at
)
SELECT DISTINCT
    cc.id as company_id,
    t.Location as location,
    t.`Customer Name` as unit_name,
    NOW() as created_at,
    NOW() as updated_at
FROM TEMPORARY_IMPORT_TABLE t
JOIN contact_companies cc ON TRIM(SUBSTRING_INDEX(t.`Customer Name`, '(', 1)) = cc.company_name;

-- Step 3: Import products
INSERT IGNORE INTO products (
    name,
    model,
    part_number,
    description,
    created_at,
    updated_at
)
SELECT DISTINCT
    t.Machine as name,
    t.Model as model,
    t.`Part NO` as part_number,
    t.`Machine Type` as description,
    NOW() as created_at,
    NOW() as updated_at
FROM TEMPORARY_IMPORT_TABLE t
WHERE t.Machine IS NOT NULL;

-- Step 4: Import assets with machine type-specific ID mapping
INSERT IGNORE INTO assets (
    air_compressor_serial_no,
    air_compressor_part_no,
    commisioned_date,
    client_id,
    air_compressor_id,
    airdryer_id,
    filter_id,
    created_at,
    updated_at
)
SELECT 
    t.`Sl NO` as air_compressor_serial_no,
    t.`Part NO` as air_compressor_part_no,
    t.new_date as commisioned_date,
    cc.id as client_id,
    CASE WHEN LOWER(t.`Machine Type`) = 'comp' THEN p.id ELSE NULL END as air_compressor_id,
    CASE WHEN LOWER(t.`Machine Type`) IN ('dryer', 'DRYER') THEN p.id ELSE NULL END as airdryer_id,
    CASE WHEN LOWER(t.`Machine Type`) = 'filter' THEN p.id ELSE NULL END as filter_id,
    NOW() as created_at,
    NOW() as updated_at
FROM TEMPORARY_IMPORT_TABLE t
JOIN contact_companies cc ON TRIM(SUBSTRING_INDEX(t.`Customer Name`, '(', 1)) = cc.company_name 
    AND t.Location = cc.company_address 
LEFT JOIN products p ON t.Machine = p.name AND t.Model = p.model;

-- Step 5: Create AMC contracts
INSERT INTO amc_contracts (
    amc_start_date,
    amc_expiry_date,
    client_id,
    created_at,
    updated_at
)
SELECT DISTINCT
    CASE 
        WHEN t.STATUS = 'WAR' THEN t.new_date
        ELSE 
            CASE 
                WHEN MONTH(t.new_date) <= MONTH(CURRENT_DATE()) THEN '2025-04-01'
                ELSE '2024-04-01'
            END
    END as amc_start_date,
    CASE 
        WHEN t.STATUS = 'WAR' THEN DATE_ADD(t.new_date, INTERVAL 2 YEAR)
        ELSE 
            CASE 
                WHEN MONTH(t.new_date) <= MONTH(CURRENT_DATE()) THEN '2026-03-31'
                ELSE '2025-03-31'
            END
    END as amc_expiry_date,
    a.client_id,
    NOW() as created_at,
    NOW() as updated_at
FROM assets a
JOIN TEMPORARY_IMPORT_TABLE t ON a.air_compressor_serial_no = t.`Sl NO`
WHERE t.STATUS IN ('AMC', 'WAR')
GROUP BY 
    a.client_id, 
    t.STATUS, 
    t.new_date;

-- Step 6: Link assets to contracts
INSERT INTO amc_contract_asset (
    amc_contract_id,
    asset_id
)
SELECT 
    ac.id as amc_contract_id,
    a.id as asset_id
FROM assets a
JOIN TEMPORARY_IMPORT_TABLE t ON a.air_compressor_serial_no = t.`Sl NO`
JOIN amc_contracts ac ON a.client_id = ac.client_id 
    AND (
        (t.STATUS = 'WAR' AND ac.amc_start_date = t.new_date)
        OR 
        (t.STATUS = 'AMC' AND 
            ((MONTH(t.new_date) <= MONTH(CURRENT_DATE()) 
                AND ac.amc_start_date = '2025-04-01')
            OR 
            (MONTH(t.new_date) > MONTH(CURRENT_DATE()) 
                AND ac.amc_start_date = '2024-04-01'))
        )
    )
WHERE t.STATUS IN ('AMC', 'WAR');

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS=1;
