ferrero-naming-tool/database/seed.sql

-- Seed data for testing and demonstration

-- Insert example tracking ID into log
INSERT INTO tracking_id_log (tracking_id, assigned_to_asset, assigned_at)
VALUES ('a7K9mP', TRUE, CURRENT_TIMESTAMP);

-- Insert example master asset
INSERT INTO master_assets (
    tracking_id,
    opentext_id,
    original_filename,
    file_extension,
    upload_directory,
    brand_code,
    brand_name,
    country_code,
    country_name,
    language_code,
    language_name,
    subject_title,
    asset_type,
    asset_type_name,
    duration_seconds,
    aspect_ratio,
    file_size_bytes,
    mime_type,
    width_px,
    height_px,
    tags,
    categories,
    description,
    status,
    ingested_by
) VALUES (
    'a7K9mP',
    'OT_12345',
    '06_RAFFAELLO_MAESTRO_SD',
    '.mp4',
    '/data/assets/2025/10/raffaello',
    'RAF',
    'RAFFAELLO',
    'GL',
    'Global',
    'en',
    'English',
    'MAESTRO SD',
    'OLV',
    'On Line Video',
    30,
    '16x9',
    52428800, -- 50MB
    'video/mp4',
    1920,
    1080,
    ARRAY['master', 'video', 'campaign'],
    ARRAY['raffaello', 'maestro'],
    'Master asset for Raffaello Maestro campaign',
    'active',
    'system'
);

-- Generate a few more example tracking IDs for demonstration
INSERT INTO tracking_id_log (tracking_id, assigned_to_asset)
VALUES
    ('b3Xk2N', FALSE),
    ('c9Qm4P', FALSE),
    ('d5Wp7R', FALSE),
    ('e1Zn8T', FALSE);

-- Insert additional example master assets
INSERT INTO master_assets (
    tracking_id,
    opentext_id,
    original_filename,
    file_extension,
    upload_directory,
    brand_code,
    brand_name,
    country_code,
    country_name,
    language_code,
    language_name,
    subject_title,
    asset_type,
    asset_type_name,
    duration_seconds,
    aspect_ratio,
    tags,
    description,
    status,
    ingested_by
) VALUES
(
    'b3Xk2N',
    'OT_12346',
    '07_NUTELLA_BREAKFAST_HD',
    '.mp4',
    '/data/assets/2025/10/nutella',
    'NUT',
    'NUTELLA',
    'IT',
    'Italy',
    'it',
    'Italian',
    'BREAKFAST HD',
    'TVC',
    'TV Commercial',
    15,
    '16x9',
    ARRAY['master', 'video', 'breakfast'],
    'Nutella breakfast campaign master',
    'active',
    'system'
),
(
    'c9Qm4P',
    'OT_12347',
    '08_KINDER_BUENO_XMAS',
    '.mp4',
    '/data/assets/2025/12/kinder-bueno',
    'BUE',
    'KINDER BUENO',
    'DE',
    'Germany',
    'de',
    'German',
    'XMAS CAMPAIGN',
    'OLV',
    'On Line Video',
    20,
    '1x1',
    ARRAY['master', 'video', 'christmas'],
    'Kinder Bueno Christmas campaign',
    'active',
    'system'
);

-- Insert example derivative assets
INSERT INTO derivative_assets (
    tracking_id,
    derivative_filename,
    file_extension,
    master_asset_id,
    language_code,
    language_name,
    asset_type,
    asset_type_name,
    duration_seconds,
    aspect_ratio,
    omg_job_number,
    spot_version,
    has_master_flag,
    status,
    created_by
) VALUES
(
    'a7K9mP',
    'RAF_CH_de_TEST_FILE_OLV_001_15S_16x9',
    '.mp4',
    (SELECT id FROM master_assets WHERE tracking_id = 'a7K9mP'),
    'de',
    'German',
    'OLV',
    'On Line Video',
    15,
    '16x9',
    '1234567',
    '001',
    FALSE,
    'active',
    'user@ferrero.com'
),
(
    'a7K9mP',
    'RAF_IT_it_ME_MOMENT_OLV_6S_1x1',
    '.mp4',
    (SELECT id FROM master_assets WHERE tracking_id = 'a7K9mP'),
    'it',
    'Italian',
    'OLV',
    'On Line Video',
    6,
    '1x1',
    '1234568',
    NULL,
    FALSE,
    'active',
    'user@ferrero.com'
);

-- Add some lifecycle events manually (in addition to those auto-created by triggers)
INSERT INTO asset_lifecycle_events (tracking_id, event_type, event_data, user_id)
VALUES
(
    'a7K9mP',
    'metadata_updated',
    '{"field": "description", "old_value": "", "new_value": "Master asset for Raffaello Maestro campaign"}'::jsonb,
    'admin@ferrero.com'
),
(
    'b3Xk2N',
    'quality_check_completed',
    '{"status": "approved", "reviewer": "quality_team", "notes": "HD quality verified"}'::jsonb,
    'qa@ferrero.com'
);

-- Grant permissions (adjust as needed for your security requirements)
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-- GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO readwrite_user;