\set ON_ERROR_STOP on

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
SELECT set_config('minerops.seed_environment', :'app_env', true);

DO $$
BEGIN
    IF current_setting('minerops.seed_environment', true) <> 'development' THEN
        RAISE EXCEPTION 'MinerOps demo seed is restricted to APP_ENV=development';
    END IF;
    IF current_database() !~* '(^|_)(dev|development|test)(_|$)' THEN
        RAISE EXCEPTION 'MinerOps demo seed requires a development or test database name; got %', current_database();
    END IF;
END $$;

CREATE TEMP TABLE demo_seed_context ON COMMIT DROP AS
SELECT users.id AS actor_id
FROM users
JOIN roles ON roles.id = users.role_id
WHERE users.is_active AND roles.name = 'Admin'
ORDER BY users.created_at, users.id
LIMIT 1;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM demo_seed_context) THEN
        RAISE EXCEPTION 'Create an active Admin account before seeding MinerOps demo data';
    END IF;
END $$;

INSERT INTO sites (id, name, description)
VALUES (
    'd0000000-0000-4000-8000-000000000001',
    'Demo Mining Site',
    'Synthetic development-only MinerOps fixture. Never use as production data.'
)
ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    updated_at = now();

INSERT INTO rooms (id, site_id, name, description, sort_order)
VALUES
    (
        'd0000000-0000-4000-8000-000000000101',
        'd0000000-0000-4000-8000-000000000001',
        'Demo Unit A',
        'Synthetic room with racks A1 through A3.',
        10
    ),
    (
        'd0000000-0000-4000-8000-000000000102',
        'd0000000-0000-4000-8000-000000000001',
        'Demo Unit B',
        'Synthetic room with racks B1 and B2.',
        20
    )
ON CONFLICT (id) DO UPDATE SET
    site_id = EXCLUDED.site_id,
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    sort_order = EXCLUDED.sort_order,
    updated_at = now();

WITH demo_miners AS (
    SELECT
        i,
        ('d1000000-0000-4000-8000-' || lpad(i::text, 12, '0'))::uuid AS id,
        CASE
            WHEN i <= 18 THEN 'd0000000-0000-4000-8000-000000000101'::uuid
            WHEN i <= 27 THEN 'd0000000-0000-4000-8000-000000000102'::uuid
            ELSE NULL
        END AS room_id,
        (ARRAY['M30S', 'M30S+', 'M50', 'M50S', 'M60', 'M60S'])[((i - 1) % 6) + 1] AS model,
        format('02:00:00:00:00:%s', upper(lpad(to_hex(i), 2, '0'))) AS mac,
        CASE
            WHEN i IN (21, 22, 23) THEN 'in_repair'
            WHEN i IN (25, 26, 29) THEN 'spare'
            WHEN i = 28 THEN 'retired'
            ELSE 'active'
        END AS lifecycle_status,
        CASE
            WHEN i IN (6, 12, 21, 22, 23, 24) THEN 'offline'
            WHEN i IN (5, 10, 20, 25, 28, 29, 30) THEN 'unknown'
            ELSE 'online'
        END AS runtime_status,
        CASE
            WHEN i IN (5, 10, 20, 25, 28, 29, 30) THEN NULL
            WHEN i = 19 THEN now() - interval '3 days'
            WHEN i IN (21, 22, 23) THEN now() - make_interval(hours => i - 17)
            ELSE now() - make_interval(mins => i * 3)
        END AS observed_at
    FROM generate_series(1, 30) AS series(i)
)
INSERT INTO miners (
    id, site_id, room_id, asset_tag, current_mac, current_ip, hostname,
    miner_sn, power_sn, control_board_sn, model, miner_type, api_version,
    firmware_version, platform, chip, status, lifecycle_status, notes,
    first_seen_at, last_seen_at, created_at, updated_at
)
SELECT
    id,
    'd0000000-0000-4000-8000-000000000001',
    room_id,
    format('DEMO-MIN-%s', lpad(i::text, 3, '0')),
    mac,
    ('192.0.2.' || i)::inet,
    format('demo-miner-%s', lpad(i::text, 3, '0')),
    format('DEMO-SERIAL-%s', lpad(i::text, 3, '0')),
    format('DEMO-PSU-%s', lpad(i::text, 3, '0')),
    format('DEMO-CB-%s', lpad(i::text, 3, '0')),
    model,
    'WhatsMiner',
    '3.2-demo',
    format('DEMO-FW-2026.%s', ((i - 1) % 4) + 1),
    'Demo Platform',
    'Synthetic ASIC',
    runtime_status,
    lifecycle_status,
    'Synthetic development-only miner. No real fleet identity or measurements.',
    now() - make_interval(days => 120 - i),
    observed_at,
    now() - make_interval(days => 120 - i),
    now()
FROM demo_miners
ON CONFLICT (id) DO UPDATE SET
    site_id = EXCLUDED.site_id,
    room_id = EXCLUDED.room_id,
    asset_tag = EXCLUDED.asset_tag,
    current_mac = EXCLUDED.current_mac,
    current_ip = EXCLUDED.current_ip,
    hostname = EXCLUDED.hostname,
    miner_sn = EXCLUDED.miner_sn,
    power_sn = EXCLUDED.power_sn,
    control_board_sn = EXCLUDED.control_board_sn,
    model = EXCLUDED.model,
    miner_type = EXCLUDED.miner_type,
    api_version = EXCLUDED.api_version,
    firmware_version = EXCLUDED.firmware_version,
    platform = EXCLUDED.platform,
    chip = EXCLUDED.chip,
    status = EXCLUDED.status,
    lifecycle_status = EXCLUDED.lifecycle_status,
    notes = EXCLUDED.notes,
    first_seen_at = EXCLUDED.first_seen_at,
    last_seen_at = EXCLUDED.last_seen_at,
    updated_at = now();

WITH room_a AS (
    SELECT
        n AS ordinal,
        'd0000000-0000-4000-8000-000000000101'::uuid AS room_id,
        'Demo Unit A'::text AS room_name,
        format('A%s', ((n - 1) / 10) + 1) AS rack_name,
        ((n - 1) % 10) / 5 + 1 AS shelf_index,
        ((n - 1) % 5) + 1 AS position_index,
        CASE WHEN n <= 18 THEN n ELSE NULL END AS miner_number
    FROM generate_series(1, 30) AS positions(n)
),
room_b AS (
    SELECT
        n + 30 AS ordinal,
        'd0000000-0000-4000-8000-000000000102'::uuid AS room_id,
        'Demo Unit B'::text AS room_name,
        format('B%s', ((n - 1) / 10) + 1) AS rack_name,
        ((n - 1) % 10) / 5 + 1 AS shelf_index,
        ((n - 1) % 5) + 1 AS position_index,
        CASE WHEN n <= 9 THEN n + 18 ELSE NULL END AS miner_number
    FROM generate_series(1, 20) AS positions(n)
),
positions AS (
    SELECT * FROM room_a
    UNION ALL
    SELECT * FROM room_b
)
INSERT INTO location_positions (
    id, room_id, room_name, rack_name, shelf_name, shelf_index,
    position_index, position_label, position_kind, position_status,
    expected_mac, miner_id, notes, source_file, source_sheet, created_at, updated_at
)
SELECT
    ('d2000000-0000-4000-8000-' || lpad(ordinal::text, 12, '0'))::uuid,
    room_id,
    room_name,
    rack_name,
    format('Shelf %s', shelf_index),
    shelf_index,
    position_index,
    format('%s-S%s-P%s', rack_name, shelf_index, position_index),
    'miner_slot',
    CASE WHEN miner_number IS NULL THEN 'available' ELSE 'occupied' END,
    CASE WHEN ordinal = 50 THEN '02:00:00:00:00:FE' ELSE NULL END,
    CASE WHEN miner_number IS NULL THEN NULL ELSE
        ('d1000000-0000-4000-8000-' || lpad(miner_number::text, 12, '0'))::uuid
    END,
    'Synthetic development-only position.',
    'MinerOps demo seed',
    'Synthetic layout',
    now(),
    now()
FROM positions
ON CONFLICT (id) DO UPDATE SET
    room_id = EXCLUDED.room_id,
    room_name = EXCLUDED.room_name,
    rack_name = EXCLUDED.rack_name,
    shelf_name = EXCLUDED.shelf_name,
    shelf_index = EXCLUDED.shelf_index,
    position_index = EXCLUDED.position_index,
    position_label = EXCLUDED.position_label,
    position_kind = EXCLUDED.position_kind,
    position_status = EXCLUDED.position_status,
    expected_mac = EXCLUDED.expected_mac,
    miner_id = EXCLUDED.miner_id,
    notes = EXCLUDED.notes,
    source_file = EXCLUDED.source_file,
    source_sheet = EXCLUDED.source_sheet,
    updated_at = now();

WITH demo_miners AS (
    SELECT
        i,
        ('d1000000-0000-4000-8000-' || lpad(i::text, 12, '0'))::uuid AS miner_id,
        CASE
            WHEN i <= 18 THEN i
            WHEN i <= 27 THEN i + 12
            ELSE NULL
        END AS position_ordinal
    FROM generate_series(1, 30) AS series(i)
)
INSERT INTO miner_location_states (
    miner_id, asset_state, location_position_id, state_notes,
    updated_by_user_id, updated_at
)
SELECT
    miner_id,
    CASE
        WHEN i IN (21, 22, 23) THEN 'repair'
        WHEN i IN (25, 26, 29) THEN 'storage'
        WHEN i = 28 THEN 'decommissioned'
        WHEN position_ordinal IS NULL THEN 'waiting_placement'
        ELSE 'shelved'
    END,
    CASE WHEN position_ordinal IS NULL THEN NULL ELSE
        ('d2000000-0000-4000-8000-' || lpad(position_ordinal::text, 12, '0'))::uuid
    END,
    'Synthetic development-only location state.',
    (SELECT actor_id FROM demo_seed_context),
    now()
FROM demo_miners
ON CONFLICT (miner_id) DO UPDATE SET
    asset_state = EXCLUDED.asset_state,
    location_position_id = EXCLUDED.location_position_id,
    state_notes = EXCLUDED.state_notes,
    updated_by_user_id = EXCLUDED.updated_by_user_id,
    updated_at = now();

WITH telemetry AS (
    SELECT
        i,
        ('d1000000-0000-4000-8000-' || lpad(i::text, 12, '0'))::uuid AS miner_id,
        CASE
            WHEN i = 19 THEN now() - interval '3 days'
            WHEN i IN (21, 22, 23) THEN now() - make_interval(hours => i - 17)
            ELSE now() - make_interval(mins => i * 3)
        END AS observed_at,
        CASE ((i - 1) % 6)
            WHEN 0 THEN 90
            WHEN 1 THEN 100
            WHEN 2 THEN 112
            WHEN 3 THEN 126
            WHEN 4 THEN 170
            ELSE 186
        END::numeric AS nominal_ths,
        i IN (6, 12, 21, 22, 23, 24) AS stopped
    FROM generate_series(1, 30) AS series(i)
    WHERE i NOT IN (5, 10, 20, 25, 28, 29, 30)
)
INSERT INTO miner_status_snapshots (
    id, miner_id, observed_ip, ip_address, mac_address, observed_at,
    mhs_av, mhs_1m, mhs_15m, hs_rt, power_w, power_rate_w_per_th,
    env_temp_c, chip_temp_min_c, chip_temp_max_c, chip_temp_avg_c,
    fan_speed_in_rpm, fan_speed_out_rpm, uptime_seconds, elapsed_seconds,
    hash_stable, pool_rejected_percent, raw_summary_json, created_at
)
SELECT
    ('d3000000-0000-4000-8000-' || lpad(i::text, 12, '0'))::uuid,
    miner_id,
    ('192.0.2.' || i)::inet,
    ('192.0.2.' || i)::inet,
    format('02:00:00:00:00:%s', upper(lpad(to_hex(i), 2, '0'))),
    observed_at,
    CASE WHEN stopped THEN 0 ELSE (nominal_ths + ((i % 5) - 2) * 0.7) * 1000000 END,
    CASE WHEN stopped THEN 0 ELSE (nominal_ths + ((i % 4) - 1) * 0.6) * 1000000 END,
    CASE WHEN stopped THEN 0 ELSE (nominal_ths + ((i % 3) - 1) * 0.4) * 1000000 END,
    CASE WHEN stopped THEN 0 ELSE (nominal_ths + ((i % 5) - 2) * 0.7) * 1000000 END,
    CASE WHEN stopped THEN 0 ELSE 2920 + nominal_ths * 4.8 + (i % 7) * 23 END,
    CASE WHEN stopped THEN NULL ELSE round((28 + (i % 5) * 0.6)::numeric, 2) END,
    24 + (i % 7),
    48 + (i % 6) * 2,
    58 + (i % 9) * 3,
    54 + (i % 8) * 2,
    CASE WHEN stopped THEN 0 ELSE 4700 + (i % 6) * 120 END,
    CASE WHEN stopped THEN 0 ELSE 4950 + (i % 5) * 135 END,
    CASE WHEN stopped THEN 0 ELSE 86400 + i * 731 END,
    CASE WHEN stopped THEN 0 ELSE 86400 + i * 731 END,
    CASE WHEN stopped THEN false WHEN i IN (9, 17) THEN false ELSE true END,
    CASE WHEN stopped THEN 0 ELSE round((0.15 + (i % 6) * 0.08)::numeric, 2) END,
    jsonb_build_object(
        'fixture', 'MinerOps development demo',
        'synthetic', true,
        'model_index', i
    ),
    observed_at
FROM telemetry
ON CONFLICT (id) DO UPDATE SET
    observed_ip = EXCLUDED.observed_ip,
    ip_address = EXCLUDED.ip_address,
    mac_address = EXCLUDED.mac_address,
    observed_at = EXCLUDED.observed_at,
    mhs_av = EXCLUDED.mhs_av,
    mhs_1m = EXCLUDED.mhs_1m,
    mhs_15m = EXCLUDED.mhs_15m,
    hs_rt = EXCLUDED.hs_rt,
    power_w = EXCLUDED.power_w,
    power_rate_w_per_th = EXCLUDED.power_rate_w_per_th,
    env_temp_c = EXCLUDED.env_temp_c,
    chip_temp_min_c = EXCLUDED.chip_temp_min_c,
    chip_temp_max_c = EXCLUDED.chip_temp_max_c,
    chip_temp_avg_c = EXCLUDED.chip_temp_avg_c,
    fan_speed_in_rpm = EXCLUDED.fan_speed_in_rpm,
    fan_speed_out_rpm = EXCLUDED.fan_speed_out_rpm,
    uptime_seconds = EXCLUDED.uptime_seconds,
    elapsed_seconds = EXCLUDED.elapsed_seconds,
    hash_stable = EXCLUDED.hash_stable,
    pool_rejected_percent = EXCLUDED.pool_rejected_percent,
    raw_summary_json = EXCLUDED.raw_summary_json,
    created_at = EXCLUDED.created_at;

INSERT INTO repairs (
    id, miner_id, status, reason, opened_at, opened_by,
    completed_at, completed_by, completion_summary, resulting_lifecycle
)
VALUES
    (
        'd4000000-0000-4000-8000-000000000001',
        'd1000000-0000-4000-8000-000000000021',
        'OPEN', 'CONTROL_BOARD', now() - interval '2 days',
        (SELECT actor_id FROM demo_seed_context),
        NULL, NULL, NULL, NULL
    ),
    (
        'd4000000-0000-4000-8000-000000000002',
        'd1000000-0000-4000-8000-000000000022',
        'WAITING_FOR_PARTS', 'PSU', now() - interval '5 days',
        (SELECT actor_id FROM demo_seed_context),
        NULL, NULL, NULL, NULL
    ),
    (
        'd4000000-0000-4000-8000-000000000003',
        'd1000000-0000-4000-8000-000000000023',
        'READY_FOR_TEST', 'HASHBOARD', now() - interval '1 day',
        (SELECT actor_id FROM demo_seed_context),
        NULL, NULL, NULL, NULL
    ),
    (
        'd4000000-0000-4000-8000-000000000004',
        'd1000000-0000-4000-8000-000000000004',
        'COMPLETED', 'PREVENTIVE_MAINTENANCE', now() - interval '21 days',
        (SELECT actor_id FROM demo_seed_context),
        now() - interval '20 days',
        (SELECT actor_id FROM demo_seed_context),
        'Synthetic scheduled inspection completed successfully.',
        'active'
    )
ON CONFLICT (id) DO NOTHING;

INSERT INTO repair_events (
    id, repair_id, sequence, event_type, created_at, created_by, payload
)
VALUES
    (
        'd5000000-0000-4000-8000-000000000001',
        'd4000000-0000-4000-8000-000000000001',
        1, 'REPAIR_STARTED', now() - interval '2 days',
        (SELECT actor_id FROM demo_seed_context),
        jsonb_build_object('reason', 'CONTROL_BOARD', 'from_lifecycle', 'active', 'to_lifecycle', 'in_repair')
    ),
    (
        'd5000000-0000-4000-8000-000000000002',
        'd4000000-0000-4000-8000-000000000001',
        2, 'INITIAL_NOTE', now() - interval '2 days' + interval '10 minutes',
        (SELECT actor_id FROM demo_seed_context),
        jsonb_build_object('note', 'Synthetic intermittent control-board communication fault.')
    ),
    (
        'd5000000-0000-4000-8000-000000000003',
        'd4000000-0000-4000-8000-000000000002',
        1, 'REPAIR_STARTED', now() - interval '5 days',
        (SELECT actor_id FROM demo_seed_context),
        jsonb_build_object('reason', 'PSU', 'from_lifecycle', 'spare', 'to_lifecycle', 'in_repair')
    ),
    (
        'd5000000-0000-4000-8000-000000000004',
        'd4000000-0000-4000-8000-000000000002',
        2, 'INITIAL_NOTE', now() - interval '5 days' + interval '15 minutes',
        (SELECT actor_id FROM demo_seed_context),
        jsonb_build_object('note', 'Synthetic replacement PSU is awaiting delivery.')
    ),
    (
        'd5000000-0000-4000-8000-000000000005',
        'd4000000-0000-4000-8000-000000000003',
        1, 'REPAIR_STARTED', now() - interval '1 day',
        (SELECT actor_id FROM demo_seed_context),
        jsonb_build_object('reason', 'HASHBOARD', 'from_lifecycle', 'active', 'to_lifecycle', 'in_repair')
    ),
    (
        'd5000000-0000-4000-8000-000000000006',
        'd4000000-0000-4000-8000-000000000003',
        2, 'INITIAL_NOTE', now() - interval '1 day' + interval '20 minutes',
        (SELECT actor_id FROM demo_seed_context),
        jsonb_build_object('note', 'Synthetic hashboard repair passed bench checks and is ready for test.')
    ),
    (
        'd5000000-0000-4000-8000-000000000007',
        'd4000000-0000-4000-8000-000000000004',
        1, 'REPAIR_STARTED', now() - interval '21 days',
        (SELECT actor_id FROM demo_seed_context),
        jsonb_build_object('reason', 'PREVENTIVE_MAINTENANCE', 'from_lifecycle', 'active', 'to_lifecycle', 'in_repair')
    ),
    (
        'd5000000-0000-4000-8000-000000000008',
        'd4000000-0000-4000-8000-000000000004',
        2, 'REPAIR_COMPLETED', now() - interval '20 days',
        (SELECT actor_id FROM demo_seed_context),
        jsonb_build_object(
            'previous_miner_lifecycle', 'in_repair',
            'resulting_miner_lifecycle', 'active',
            'pre_repair_lifecycle', 'active',
            'completion_summary', 'Synthetic scheduled inspection completed successfully.'
        )
    )
ON CONFLICT (id) DO NOTHING;

INSERT INTO miner_maintenance_events (
    id, miner_id, event_type, event_status, event_title, description,
    event_at, created_by_user_id, created_at, updated_at
)
VALUES
    (
        'd6000000-0000-4000-8000-000000000001',
        'd1000000-0000-4000-8000-000000000021',
        'repair', 'open', 'Control board diagnostics',
        'Synthetic repair projection. Intermittent control-board communication fault.',
        now() - interval '2 days', (SELECT actor_id FROM demo_seed_context), now(), now()
    ),
    (
        'd6000000-0000-4000-8000-000000000002',
        'd1000000-0000-4000-8000-000000000022',
        'repair', 'waiting_parts', 'Awaiting replacement PSU',
        'Synthetic repair projection. Replacement PSU is awaiting delivery.',
        now() - interval '5 days', (SELECT actor_id FROM demo_seed_context), now(), now()
    ),
    (
        'd6000000-0000-4000-8000-000000000003',
        'd1000000-0000-4000-8000-000000000023',
        'repair', 'testing', 'Hashboard validation',
        'Synthetic repair projection. Repaired hashboard is ready for a controlled test.',
        now() - interval '1 day', (SELECT actor_id FROM demo_seed_context), now(), now()
    ),
    (
        'd6000000-0000-4000-8000-000000000004',
        'd1000000-0000-4000-8000-000000000004',
        'repair', 'completed', 'Scheduled inspection complete',
        'Synthetic historical repair projection.',
        now() - interval '20 days', (SELECT actor_id FROM demo_seed_context), now(), now()
    )
ON CONFLICT (id) DO UPDATE SET
    event_status = EXCLUDED.event_status,
    event_title = EXCLUDED.event_title,
    description = EXCLUDED.description,
    event_at = EXCLUDED.event_at,
    created_by_user_id = EXCLUDED.created_by_user_id,
    updated_at = now();

DO $$
DECLARE
    miner_count integer;
    position_count integer;
    repair_count integer;
BEGIN
    SELECT count(*) INTO miner_count FROM miners WHERE asset_tag LIKE 'DEMO-MIN-%';
    SELECT count(*) INTO position_count FROM location_positions WHERE source_file = 'MinerOps demo seed';
    SELECT count(*) INTO repair_count FROM repairs WHERE id::text LIKE 'd4000000-0000-4000-8000-%';
    IF miner_count <> 30 OR position_count <> 50 OR repair_count <> 4 THEN
        RAISE EXCEPTION 'Demo seed verification failed: miners %, positions %, repairs %',
            miner_count, position_count, repair_count;
    END IF;
END $$;

COMMIT;