supabase_setup.sql

-- ==========================================
-- SUPABASE SCHEMA & SEED DATA SETUP
-- Copy & paste this into the Supabase SQL Editor
-- ==========================================

-- 1. Create Timeline Table
CREATE TABLE IF NOT EXISTS timeline (
    id TEXT PRIMARY KEY,
    role TEXT NOT NULL,
    company TEXT NOT NULL,
    location TEXT,
    date_range TEXT NOT NULL,
    bullets TEXT[] NOT NULL,
    media JSONB[] DEFAULT '{}',
    sort_order INT NOT NULL
);

-- Enable RLS & Select policy for Timeline
ALTER TABLE timeline ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access for timeline" ON timeline FOR SELECT USING (true);

-- Add enriched data columns (safe to run on existing DB)
ALTER TABLE timeline
  ADD COLUMN IF NOT EXISTS job_skills JSONB DEFAULT '[]',
  ADD COLUMN IF NOT EXISTS employer_list JSONB DEFAULT '[]';

-- 2. Create Podcasts Table
CREATE TABLE IF NOT EXISTS podcasts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    host TEXT NOT NULL,
    frequency TEXT NOT NULL,
    apple_podcasts_url TEXT,
    status TEXT NOT NULL,
    notes TEXT
);

-- Enable RLS & Select policy for Podcasts
ALTER TABLE podcasts ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access for podcasts" ON podcasts FOR SELECT USING (true);

-- 3. Create Portfolio Table
CREATE TABLE IF NOT EXISTS portfolio (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    category TEXT NOT NULL,
    description TEXT NOT NULL,
    image_path TEXT NOT NULL,
    tags TEXT[] NOT NULL
);

-- Enable RLS & Select policy for Portfolio
ALTER TABLE portfolio ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access for portfolio" ON portfolio FOR SELECT USING (true);

-- 4. Create Socials Table
CREATE TABLE IF NOT EXISTS socials (
    id SERIAL PRIMARY KEY,
    category TEXT NOT NULL, -- 'primary' or 'community'
    platform TEXT NOT NULL, -- e.g. 'linkedin', 'substack', 'tiktok'
    url TEXT NOT NULL,
    handle TEXT NOT NULL,
    title TEXT NOT NULL
);

-- Enable RLS & Select policy for Socials
ALTER TABLE socials ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access for socials" ON socials FOR SELECT USING (true);

-- 5. Create Skills Table
CREATE TABLE IF NOT EXISTS skills (
    id SERIAL PRIMARY KEY,
    category TEXT NOT NULL, -- 'leadership' or 'comms'
    name TEXT NOT NULL
);

-- Enable RLS & Select policy for Skills
ALTER TABLE skills ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access for skills" ON skills FOR SELECT USING (true);

-- 6. Create Education Table
CREATE TABLE IF NOT EXISTS education (
    id SERIAL PRIMARY KEY,
    institution TEXT NOT NULL,
    details TEXT NOT NULL
);

-- Enable RLS & Select policy for Education
ALTER TABLE education ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access for education" ON education FOR SELECT USING (true);

-- 7. Create Testimonials Table
CREATE TABLE IF NOT EXISTS testimonials (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    title TEXT NOT NULL,
    company TEXT,
    content TEXT NOT NULL,
    image_url TEXT,
    linkedin_url TEXT,
    sort_order INTEGER DEFAULT 0,
    is_enabled BOOLEAN DEFAULT true
);

-- Enable RLS & Select policy for Testimonials
ALTER TABLE testimonials ENABLE ROW LEVEL SECURITY;
CREATE POLICY "testimonials_public_read" ON testimonials FOR SELECT USING (true);
CREATE POLICY "testimonials_service_only" ON testimonials FOR ALL USING (auth.role() = 'service_role');


-- ==========================================
-- SEED DATA STATEMENTS
-- ==========================================

-- Seed Timeline
TRUNCATE timeline RESTART IDENTITY CASCADE;
INSERT INTO timeline (id, role, company, location, date_range, bullets, media, sort_order) VALUES
('redwood-empire', 'Partner / Producer / Editor', 'Redwood Empire Media', NULL, '02/2025 - Present', 
 ARRAY[
   'Complete in-person and remote video podcast production.',
   'Creation of premium short and long-form video content.',
   'Professional audio and video editing.',
   'Content strategy for individuals and businesses to increase online visibility.'
 ], 
 ARRAY[
   '{"title": "Studio Recording Setup Photo", "req": "1920x1080px (PNG/JPG)"}'::jsonb,
   '{"title": "Podcast Production Still", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 1),

('seiu-1021', 'Field Representative', 'SEIU 1021', 'Santa Rosa, CA', '07/2023 - 06/2025', 
 ARRAY[
   'Led contract negotiations for the California Academy of Sciences, developing effective communication strategies.',
   'Bargained contracts and represented union members in public education sectors.',
   'Managed grievance presentation, member defense, and contract enforcement procedures.'
 ], 
 ARRAY[
   '{"title": "California Academy of Sciences Rally", "req": "1920x1080px (PNG/JPG)"}'::jsonb,
   '{"title": "Bargaining Committee Sessions", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 2),

('teamsters-harris', 'Director', 'Teamsters for Harris', 'National Campaign', '07/2024 - 11/2024', 
 ARRAY[
   'Led digital organizing campaign, growing online communities by 10,000+ followers on X/Twitter and 3,000+ on Facebook in just two months.',
   'Managed cross-platform ad campaigns (X, Meta, Google) targeting union member recruitment and mobilization.',
   'Created dynamic digital assets, educational videos, and messaging frameworks tailored for labor audiences.',
   'Built a nationwide grassroots coalition leading to local/regional endorsements covering over 1 million union members.'
 ], 
 ARRAY[
   '{"title": "Campaign Design & Banner Graphics", "req": "1920x1080px (PNG/JPG)"}'::jsonb,
   '{"title": "Ad Campaign Analytics Graph", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 3),

('apple', 'Specialist', 'Apple', 'Corte Madera, CA', '10/2021 - 08/2023', 
 ARRAY[
   'Maintained 95% customer satisfaction rating through empathetic, clear communication and high-impact troubleshooting.',
   'Developed targeted messaging and educational walkthroughs for a highly diverse consumer demographic.',
   'Leveraged modern technology suites to deliver premium client experiences.'
 ], 
 ARRAY[
   '{"title": "Apple Store Corte Madera Still", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 4),

('norcal-pods', 'Content Producer', 'NorCal Pods', 'San Francisco Bay Area', '09/2020 - 02/2023', 
 ARRAY[
   'Produced over 150 podcast episodes, expanding distribution channels to capture thousands of active listeners.',
   'Provided targeted marketing, SEO optimization, and algorithm tuning to maximize audience viewership and engagement.'
 ], 
 ARRAY[
   '{"title": "NorCal Pods Episode Cover Designs", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 5),

('freelance', 'Independent Communications Consultant', 'Freelance', 'Streaming Focus', '10/2020 - 08/2023', 
 ARRAY[
   'Advised and guided creators to expand, distribute, and monetize interactive digital content.',
   'Created and edited multi-format creative assets including print layout, graphic design, audio, and video streams.',
   'Strategized distribution pipelines to enhance structural SEO and organic viewer conversions.'
 ], 
 ARRAY[
   '{"title": "Stream Overlay & Branding Layouts", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 6),

('healthy-democracy', 'Technology and Logistics Specialist', 'Healthy Democracy', 'Portland, OR', '06/2022 - 08/2022', 
 ARRAY[
   'Managed digital streaming and communication technology pipelines for citizen engagement assemblies.',
   'Guaranteed seamless technical operations and low-latency broadcast systems for public participation processes.'
 ], 
 ARRAY[
   '{"title": "Citizen Assembly Broadcaster Control Desk", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 7),

('teamsters-853', 'Business Representative / Communications', 'Teamsters Local 853', 'Oakland, CA', '01/2014 - 10/2020', 
 ARRAY[
   'Managed comprehensive communications initiatives prior to the formation of a formal department.',
   'Negotiated, maintained, and enforced contracts. Handled grievance presentation and arbitration file preparation.',
   'Produced worker-facing media to train, onboard, and explain labor rights and benefit designs.',
   'Developed internal and external communications frameworks supporting dynamic local organizing drives.',
   'Planned and orchestrated large-scale union events and assemblies.'
 ], 
 ARRAY[
   '{"title": "Union General Membership Meeting", "req": "1920x1080px (PNG/JPG)"}'::jsonb,
   '{"title": "Picket Lines & Member Mobilization", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 8),

('teamsters-665', 'Business Representative', 'Teamsters Local 665', 'Santa Rosa, CA', '01/2012 - 01/2014', 
 ARRAY[
   'Bargained labor agreements, represented member interests, and conducted workplace audits and contract enforcement.'
 ], 
 ARRAY[
   '{"title": "Local 665 Member Action Still", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 10),

('teamsters-624', 'President', 'Teamsters Local 624', NULL, '06/2006 - 01/2012', 
 ARRAY[
   'Elected Local Union President, administering executive operations, financial budgeting, and strategic labor campaigns.',
   'Supervised all representational departments, business agents, and organizing drives.'
 ], 
 ARRAY[
   '{"title": "Local 624 Union Hall & President Assembly", "req": "1920x1080px (PNG/JPG)"}'::jsonb
 ], 11);

-- Seed Podcasts
TRUNCATE podcasts RESTART IDENTITY CASCADE;
INSERT INTO podcasts (title, host, frequency, apple_podcasts_url, status, notes) VALUES
('Buy The Bay', 'Dan Ancheta', 'Weekly', 'https://podcasts.apple.com/us/podcast/buy-the-bay-the-bay-area-real-estate-podcast/id1653134914', 'active', NULL),
('BenefitsTV', 'Andrew McNeil & Rosario Avila', 'Every Other Week', 'https://podcasts.apple.com/us/podcast/benefitstv-the-podcast/id1612739328', 'active', NULL),
('Lash Girls Don''t Cry', 'Niomi & Kayla', 'Weekly', 'https://podcasts.apple.com/us/podcast/lash-girls-dont-cry/id1527632152', 'ended', NULL),
('Leave It Better', 'Brandon Trammell', 'Every Other Week', 'https://podcasts.apple.com/us/podcast/leave-it-better-w-brandon-trammell/id1674488330', 'active', NULL),
('The Yin Project', 'Emily Beaven', 'Every Other Week', 'https://podcasts.apple.com/us/podcast/the-yin-project/id1614742967', 'active', NULL),
('The Redwood Empire', 'Phil Ybarrolaza', 'Varies', 'https://podcasts.apple.com/us/podcast/the-redwood-empire/id1552594611', 'active', NULL),
('That''s the Whiskey', 'Jasmine Cruz & Caroline Moeller', 'Every Other Week', 'https://podcasts.apple.com/us/podcast/thats-the-whiskey/id1573024982', 'active', NULL),
('RealWise Sonoma County', 'Stephanie Johnson', 'Varies', 'https://podcasts.apple.com/us/podcast/realwise-sonoma-county-a-real-estate-podcast-with/id1696205937', 'active', NULL),
('Unionist', 'Phil Ybarrolaza', 'Varies', 'https://podcasts.apple.com/us/podcast/unionist/id1534015691', 'active', NULL),
('AI for the Normal Guy', 'Phil Ybarrolaza', 'Varies', 'https://podcasts.apple.com/us/podcast/ai-for-the-normal-guy/id1736639446', 'active', NULL),
('Just Show Up', 'Julian Solano & Michael Williams', 'Weekly', NULL, 'no_apple_podcasts_match', 'Hosted by Julian & Michael. Video/Excellerate Real Estate content exists, but no standalone active show on Apple Podcasts.'),
('The Interview Series', 'Jeff Fuller', 'Varies', NULL, 'no_apple_podcasts_match', 'Produced by Redwood Empire Media. Distributed on other platforms (JioSaavn/Linktree), no standalone show on Apple Podcasts.'),
('BBL', 'Bianca Broos', 'Varies', NULL, 'no_apple_podcasts_match', 'Hosted by Bianca Broos. Guest appearances exist, but no standalone active show under this name on Apple Podcasts.');

-- Seed Portfolio
TRUNCATE portfolio RESTART IDENTITY CASCADE;
INSERT INTO portfolio (id, title, category, description, image_path, tags) VALUES
('tfh-banner', 'Teamsters for Harris Campaign Header', 'campaigns', 'National digital campaign header used across social channels during the 2024 presidential mobilization.', 'assets/portfolio/tfh-banner.png', ARRAY['Digital Banner', 'Social Media', 'Teamsters']),
('labor-rally-flyer', 'Academy of Sciences Rally Flyer', 'flyers', 'Mobilization flyer designed for the SEIU 1021 contract campaign at the California Academy of Sciences.', 'assets/portfolio/rally-flyer.png', ARRAY['Print Flyer', 'SEIU 1021', 'Member Rally']),
('redwood-media-logo', 'Redwood Empire Media Branding', 'branding', 'Visual identity design, logo variations, and style guides for Redwood Empire Media content network.', 'assets/portfolio/rem-branding.png', ARRAY['Logo Design', 'Style Guide', 'Vector']),
('unionist-artwork', 'Unionist Podcast Cover Artwork', 'branding', 'Podcast album cover art designed for the Unionist show, depicting labor solidarity and voice.', 'assets/portfolio/unionist-art.png', ARRAY['Cover Art', 'Typography', 'Illustrator']),
('local853-picket-flyer', 'Teamsters 853 Strike Support Flyer', 'flyers', 'Local union support materials designed to coordinate picket lines, shift schedules, and community donation drives.', 'assets/portfolio/strike-support.png', ARRAY['Print Flyer', 'Campaign Outreach', 'Teamsters 853']),
('harris-campaign-social-ad', 'National Organizing Social Ad', 'campaigns', 'Targeted social media ad creatives designed for Facebook and Instagram during the Teamsters for Harris mobilization.', 'assets/portfolio/social-ad.png', ARRAY['Ad Creative', 'Social Media', 'Harris-Walz']);

-- Seed Socials
TRUNCATE socials RESTART IDENTITY CASCADE;
INSERT INTO socials (category, platform, url, handle, title) VALUES
('primary', 'linkedin', 'https://linkedin.com/in/philybarrolaza', 'linkedin.com/in/philybarrolaza', 'LinkedIn'),
('primary', 'instagram', 'https://instagram.com/getphily', '@getphily', 'Instagram'),
('primary', 'x', 'https://x.com/thegetphily', '@thegetphily', 'X (Twitter)'),
('primary', 'youtube', 'https://www.youtube.com/channel/UCbXMbRy8d4s3zK-zc_2ia8Q', 'YouTube Channel', 'YouTube'),
('community', 'substack', 'https://substack.com/@getphily', '@getphily', 'Substack'),
('community', 'tiktok', 'https://tiktok.com/@.getphily', '@.getphily', 'TikTok'),
('community', 'threads', 'https://www.threads.net/@getphily', '@getphily', 'Threads'),
('community', 'twitch', 'https://www.twitch.tv/getphily', 'getphily', 'Twitch'),
('community', 'discord', 'https://discord.gg/jaTqahFj', 'Join Community', 'Discord'),
('community', 'spotify', 'https://open.spotify.com/playlist/3xJduo2qI3XjEyYvO4oOoz?si=90241d67d9d84b34', 'Phil''s Playlists', 'Spotify'),
('community', 'merch', 'https://getphily.creator-spring.com', 'Shop Getphily', 'Merch Store'),
('community', 'facebook', 'https://www.facebook.com/philybar', 'philybar', 'Facebook'),
('community', 'email', 'mailto:phil624@gmail.com', 'phil624@gmail.com', 'Direct Email');

-- Seed Skills
TRUNCATE skills RESTART IDENTITY CASCADE;
INSERT INTO skills (category, name) VALUES
('leadership', 'First Contracts & Collective Bargaining'),
('leadership', 'Contract Costing & Financial Analysis'),
('leadership', 'Coalition Building & Strategic Campaigns'),
('leadership', 'Grievance Writing, Case Management & Panels'),
('leadership', 'Organizing Drives & Project Management'),
('leadership', 'Steward Training & Member Engagement'),
('leadership', 'Leadership Recruitment & Team Coordination'),
('comms', 'Digital Organizing & Social Media Management'),
('comms', 'SEO, Website Development & Digital Algorithms'),
('comms', 'Video Podcast Production & Editing'),
('comms', 'Graphic Layout (Photoshop, Illustrator, InDesign)'),
('comms', 'Audio & Video Editing (Final Cut Pro, Audacity, CapCut)'),
('comms', 'Content Strategy & Campaign Messaging'),
('comms', 'Fiduciary Trust Health Plan Design & Budgeting');

-- Seed Education
TRUNCATE education RESTART IDENTITY CASCADE;
INSERT INTO education (institution, details) VALUES
('Cuesta College', 'Economics Coursework | 1989 - 1990'),
('Santa Rosa Junior College', 'Political Science, Economics Coursework | 1988 - 1989');

-- 7. Create Employers Table
CREATE TABLE IF NOT EXISTS employers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    industry TEXT NOT NULL,
    location TEXT NOT NULL
);

-- Enable RLS & Select policy for Employers
ALTER TABLE employers ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access for employers" ON employers FOR SELECT USING (true);

-- Seed Employers
TRUNCATE employers RESTART IDENTITY CASCADE;
INSERT INTO employers (name, industry, location) VALUES
('Santa Rosa Junior College (SRJC)', 'Education', 'Sonoma County, CA (Santa Rosa & Petaluma)'),
('Cotati-Rohnert Park Unified School District', 'Education', 'Sonoma County, CA (Rohnert Park / Cotati)'),
('Geyserville School District (GUSD)', 'Education', 'Geyserville, CA'),
('Sonoma Valley Unified School District (SUSD)', 'Education', 'Sonoma, CA'),
('California Academy of Sciences', 'Non-Profit / Cultural', 'San Francisco, CA'),
('Loop Transportation', 'Transportation & Logistics', 'Menlo Park, CA'),
('SuperShuttle of San Francisco, Inc.', 'Transportation & Logistics', 'Burlingame, CA'),
('Central Parking System / New South Parking', 'Transportation & Logistics', 'San Francisco, CA'),
('Golden State Lumber, Inc.', 'Manufacturing & Construction', 'Newark, Brisbane, and San Rafael, CA'),
('Pacific Supply', 'Manufacturing & Construction', 'San Rafael, CA'),
('Stewart Chevrolet', 'Automotive Services', 'Colma, CA'),
('The Press Democrat', 'Media & Publishing', 'Santa Rosa / North Bay, CA'),
('ABF Freight', 'Transportation & Logistics', 'Santa Rosa, CA'),
('NeilMed Products, Inc.', 'Manufacturing & Construction', 'Santa Rosa, CA'),
('DHL Express', 'Transportation & Logistics', 'Santa Rosa, CA'),
('Farmer Brothers Coffee', 'Food & Beverage', 'Santa Rosa, CA'),
('USF Reddaway', 'Transportation & Logistics', 'Santa Rosa, CA'),
('Aramark Uniform Services', 'Facilities & Services', 'North Bay Area, CA'),
('North Bay Corporation', 'Environmental & Waste Management', 'Sonoma and Marin Counties, CA'),
('Marin Sanitary Service', 'Environmental & Waste Management', 'Marin County, CA'),
('Lumber and Mill Employers Association (LAMEA)', 'Manufacturing & Construction', 'San Mateo / East Bay / North Bay Regions, CA'),
('Serramonte Ford', 'Automotive Services', 'Colma / Daly City Area, CA'),
('Alsco / Steiner Corp.', 'Facilities & Services', 'North Bay Regional Network, CA'),
('Young’s Market', 'Food & Beverage', 'North Bay Regional Network, CA'),
('Kings County Truck Lines', 'Transportation & Logistics', 'Northern California Region'),
('Dairymen’s Feed', 'Agriculture', 'North Bay Regional Network, CA'),
('Lucero Trucking', 'Transportation & Logistics', 'Ukiah, CA'),
('Airborne Express', 'Transportation & Logistics', 'Corte Madera, CA'),
('Curtin Air Freight, Inc.', 'Transportation & Logistics', 'Petaluma, CA'),
('Unipart Services America, Inc.', 'Automotive Services', 'Brisbane, CA'),
('Buchanan Food Service', 'Food & Beverage', 'Rohnert Park, CA'),
('First Student', 'Transportation & Logistics', 'Santa Rosa/San Jose, CA'),
('Vella Cheese', 'Food & Beverage', 'Sonoma, CA'),
('Petaluma Poultry Processors', 'Food & Beverage', 'Petaluma, CA'),
('Lace House Linen', 'Facilities & Services', 'North Bay Regional Network, CA'),
('Luxor Cab', 'Transportation & Logistics', 'San Francisco / Bay Area, CA'),
('Mill Valley Refuse Service & Recycling', 'Environmental & Waste Management', 'Mill Valley / Marin County, CA'),
('McPhail’s Fuel Company', 'Energy & Utilities', 'North Bay Regional Network, CA'),
('Yellow Cab', 'Transportation & Logistics', 'San Francisco / Bay Area, CA'),
('Yellow Freight & Roadway Freight', 'Transportation & Logistics', 'Regional Distribution Hubs, CA'),
('P & S Sales, Inc.', 'Retail & Distribution', 'Hayward, CA'),
('AutoWest Honda', 'Automotive Services', 'Bay Area Region, CA'),
('Storer Transportation', 'Transportation & Logistics', 'Hayward, CA'),
('ABM', 'Facilities & Services', 'Oakland, CA'),
('Sims Metal Management', 'Environmental & Waste Management', 'Redwood City & San Jose, CA'),
('SFO Shuttle Bus Company', 'Transportation & Logistics', 'San Francisco / Bay Area, CA'),
('Park ''N Fly Service, LLC', 'Transportation & Logistics', 'Oakland, CA'),
('Mercedes-Benz of Oakland', 'Automotive Services', 'Oakland, CA'),
('WeDriveU, Inc.', 'Transportation & Logistics', 'Bay Area, CA'),
('Steeler, Inc.', 'Manufacturing & Construction', 'Newark, CA'),
('Clean Harbors Environmental Services, Inc.', 'Environmental & Waste Management', 'South San Francisco, CA'),
('Douglas Parking LLC', 'Transportation & Logistics', 'Oakland, CA'),
('Valet Hospitality Service', 'Transportation & Logistics', 'Oakland, CA'),
('Encore / Horizon Coach Lines / TMS', 'Transportation & Logistics', 'San Francisco, CA'),
('Zenith American Solutions', 'Financial & Administrative Services', 'Alameda, CA'),
('Durham School Services', 'Transportation & Logistics', 'Oakland, CA'),
('First Transit', 'Transportation & Logistics', 'Redwood City, CA'),
('Farmers Produce Corporation', 'Food & Beverage', 'Oakland, CA'),
('Golden Gate Truck Center', 'Automotive Services', 'Oakland, CA'),
('LAZ Parking', 'Transportation & Logistics', 'Oakland, CA'),
('Propark', 'Transportation & Logistics', 'Oakland, CA'),
('San Pablo Automotive', 'Automotive Services', 'Martinez, CA'),
('Oakland Honda', 'Automotive Services', 'Oakland, CA'),
('HW McKevitt', 'Facilities & Services', 'Berkeley & San Leandro, CA'),
('Pregis', 'Manufacturing & Construction', 'Hayward, CA'),
('Oakland, City of', 'Government', 'Oakland, CA'),
('UPS Freight, Inc.', 'Transportation & Logistics', 'Santa Rosa, CA'),
('Clover Stornetta', 'Food & Beverage', 'Petaluma, CA'),
('Sara Lee', 'Food & Beverage', 'Santa Rosa, CA'),
('Hansel Ford', 'Automotive Services', 'Santa Rosa, CA'),
('Laidlaw Transit', 'Transportation & Logistics', 'Lake County, CA'),
('Laidlaw School Bus', 'Transportation & Logistics', 'Santa Rosa, CA'),
('For Whom Productions, LLC', 'Production Companies & Studio Entities', 'San Francisco, CA'),
('Backyard Productions', 'Production Companies & Studio Entities', 'Manhattan Beach, CA'),
('Kaboom Productions, Inc.', 'Production Companies & Studio Entities', 'San Francisco, CA'),
('Epoch Films', 'Production Companies & Studio Entities', 'Los Angeles, CA'),
('The Cartel', 'Production Companies & Studio Entities', 'Los Angeles, CA'),
('Shocking Bottle, LLC', 'Production Companies & Studio Entities', 'Sonoma, CA');

-- 8. Create Competencies Table
CREATE TABLE IF NOT EXISTS competencies (
    id SERIAL PRIMARY KEY,
    group_type TEXT NOT NULL,
    category TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    sort_order INT NOT NULL
);

-- Enable RLS & Select policy for Competencies
ALTER TABLE competencies ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Allow public read access for competencies" ON competencies FOR SELECT USING (true);

-- Seed Competencies
TRUNCATE competencies RESTART IDENTITY CASCADE;
INSERT INTO competencies (group_type, category, name, description, sort_order) VALUES
('strategic_core', 'High-Stakes Collective Bargaining & Contract Architecture', 'Comprehensive Contract Auditing', 'Demonstrated ability to build, maintain, and manage extensive 15-to-20 Article contract structures, tracking moving target clauses from initial proposal through tentative agreements (TAs) and final document sign-offs.', 1),
('strategic_core', 'High-Stakes Collective Bargaining & Contract Architecture', 'Economic Strategy Frameworks', 'Expert at engineering multi-year economic packages, including the formulation of wage escalations, cost-of-living adjustments (COLAs), retirement programs, and stipend structures for vulnerable worker populations.', 2),
('strategic_core', 'High-Stakes Collective Bargaining & Contract Architecture', 'Language Harmonization', 'Proven experience modernizing legacy contracts by integrating local Letters of Understanding (LOUs) into unified national master templates.', 3),
('strategic_core', 'Strategic Campaign Organizing & Workforce Unit Mapping', 'High-Resolution Site Mapping', 'Skilled at designing synchronized on-the-ground organizing campaigns, explicitly scheduling team deployments, tracking department-by-department shift patterns, and mapping employee break rooms to maximize contact coverage.', 4),
('strategic_core', 'Strategic Campaign Organizing & Workforce Unit Mapping', 'Targeted Outreach Systems', 'Adept at executing structured digital and telephonic outreach funnels, interpreting real-time worker sentiment metrics, and maintaining call logging systems to convert non-members.', 5),
('strategic_core', 'Strategic Campaign Organizing & Workforce Unit Mapping', 'Objection Demolition & Messaging', 'Capable of synthesizing quick-response talking points to handle worker hesitations regarding dues investments, benefit changes, and collective voice importance.', 6),
('strategic_core', 'Regulatory Compliance & Forensic Grievance Administration', 'Public and Private Labor Law Navigation', 'Experienced managing formal administrative actions across competing jurisdictions, including the National Labor Relations Board (NLRB) decertification defenses and Unfair Labor Practice (ULP) filings.', 7),
('strategic_core', 'Regulatory Compliance & Forensic Grievance Administration', 'Advanced Grievance Resolutions', 'Adept at escalating workplace disputes through rigorous multi-level grievance procedures to recover back-pay and restore employee seniority.', 8),
('strategic_core', 'Regulatory Compliance & Forensic Grievance Administration', 'Joint Labor-Management Committee (JLMC) Leadership', 'Skilled at conducting recurring meet-and-confers, addressing structural employer reorganizations, and enforcing trust fund compliance or classification studies.', 9),
('strategic_core', 'Operational Leadership & Data Analytics', 'Total Workforce Representation (TWR) Matrix Tracking', 'Highly proficient at building metrics-driven dashboards to monitor chapter density percentiles against organizational growth goals.', 10),
('strategic_core', 'Operational Leadership & Data Analytics', 'Data-Driven Field Analytics', 'Experienced in executing and distilling large-scale qualitative member bargaining surveys into core action items.', 11),
('strategic_core', 'Operational Leadership & Data Analytics', 'Financial & Resource Administration', 'Knowledgeable in organizing data, processing complex expenditure records, and handling information requests in full compliance with public and private accounting standards.', 12),
('core_professional', 'Labor Relations & Collective Bargaining Strategy', 'Successor Contract Negotiations', 'Expert in drafting, editing, and executing multi-party collective bargaining agreements (CBAs) from initial Sunshine proposals through tentative agreements (TAs) to final ratification.', 1),
('core_professional', 'Labor Relations & Collective Bargaining Strategy', 'Economic Package Modeling', 'Skilled in analyzing financial indicators, budgets, and IRS Form 990s to architect wage scales, health/welfare contribution models, cost-of-living adjustments (COLAs), and special equity increases.', 2),
('core_professional', 'Labor Relations & Collective Bargaining Strategy', 'CBA Structural Integration', 'Adept at auditing legacy frameworks and seamlessly consolidating local Memorandums of Understanding (MOUs) and Letters of Understanding (LOUs) into unified master corporate agreements.', 3),
('core_professional', 'Labor Relations & Collective Bargaining Strategy', 'Strategic Escalation Management', 'Proven ability to navigate high-stakes bargaining impasses, organize legal strike sanctions, file federal dispute notices, and leverage mediation pathways (such as the FMCS).', 4),
('core_professional', 'Conflict Resolution & Forensic Grievance Administration', 'Multi-Step Grievance Processing', 'Competent in managing complex, multi-tiered grievance workflows, documenting contractual violations, conducting witness interrogations, and securing binding settlement terms.', 5),
('core_professional', 'Conflict Resolution & Forensic Grievance Administration', 'Just Cause Evaluation', 'Thorough understanding of structural investigative frameworks (e.g., Daugherty’s Seven Tests) to challenge unjust discipline, suspension, or termination actions.', 6),
('core_professional', 'Conflict Resolution & Forensic Grievance Administration', 'Joint Labor-Management Committee (JLMC) Leadership', 'Experienced in establishing and chairing recurring labor-management units to iron out shop-floor disputes, safety standards, and unilateral employer policy updates.', 7),
('core_professional', 'Conflict Resolution & Forensic Grievance Administration', 'Back-Pay and Seniority Restorations', 'Experienced in auditing employer payroll logs and roster metrics to resolve misclassified wage structures, lost overtime opportunities, and delayed step advancements.', 8),
('core_professional', 'Strategic Internal Organizing & Campaign Logistics', 'Worksite Unit Mapping', 'Expert in conducting visual, department-by-department workforce assessments and shift schedules to deploy union campaigns with maximum exposure.', 9),
('core_professional', 'Strategic Internal Organizing & Campaign Logistics', 'Total Workforce Representation (TWR) Oversight', 'Highly proficient in tracking member density percentages, processing check-off authorizations, and executing targeted campaigns to transition non-members into active participants.', 10),
('core_professional', 'Strategic Internal Organizing & Campaign Logistics', 'New Employee Orientation (NEO) Deployment', 'Adept at designing standardized, scaleable digital and video script onboarding tools to optimize union entry enrollment programs.', 11),
('core_professional', 'Strategic Internal Organizing & Campaign Logistics', 'Objection Demolition & Strategic Communications', 'Skilled in crafting quick-response messaging to neutralize worker pushback regarding dues structures, benefit packages, and personal workplace security.', 12),
('core_professional', 'Regulatory Compliance & Policy Analysis', 'NLRB & Public Sector Statutory Defense', 'Well-versed in defending union certifications against decertification petitions, filing unfair labor practice (ULP) charges, and managing board representation hearings.', 13),
('core_professional', 'Regulatory Compliance & Policy Analysis', 'Forensic Information Requests', 'Expert in engineering exhaustive pre-bargaining information requests targeting granular employee demographics, multi-year audited financial positions, overhead cost roll-ups, and contractor utilization data.', 14),
('core_professional', 'Regulatory Compliance & Policy Analysis', 'Workplace Health, Safety & Compliance Auditing', 'Capable of interpreting OSHA logs, Workers\' Compensation claim files, Workplace Violence Prevention Policies, and active classification/retention reports.', 15),
('core_professional', 'Regulatory Compliance & Policy Analysis', 'Employment Law Mastery', 'Comprehensive knowledge of worker safety nets, including FMLA/CFRA parameters, State Disability Insurance (SDI), Paid Family Leave (PFL), and the Americans with Disabilities Act (ADA) accommodation frameworks.', 16),
('core_professional', 'Professional Affiliations & Systems Experience', 'Union Administration Systems', 'Processing dynamic member data exports via CSV/Excel reporting frameworks to optimize campaign communications.', 17),
('core_professional', 'Professional Affiliations & Systems Experience', 'Sectors Represented', 'Public Education (K-12 & Community College Districts), Private-Sector Logistics, Regional Passenger Transportation/Shuttle Infrastructure, Public Health Valet Portfolios, and Specialized Scientific/Non-profit Cultural Institutions.', 18),
('skills_list', 'Labor Relations & Collective Bargaining', 'Collective Bargaining Agreements (CBA)', 'Drafting, structuring, and maintaining comprehensive labor agreements covering wages, working conditions, benefits, and grievance procedures.', 1),
('skills_list', 'Labor Relations & Collective Bargaining', 'Successor Contract Negotiations', 'Leading bargaining committee sessions to renew, extend, or renegotiate contract terms, managing moving proposals from initial package to final document.', 2),
('skills_list', 'Labor Relations & Collective Bargaining', 'Memorandums of Understanding (MOU)', 'Drafting and negotiating specialized side-letters and mid-term agreements to address changing operational needs or temporary agreements.', 3),
('skills_list', 'Labor Relations & Collective Bargaining', 'Letters of Understanding (LOU)', 'Formulating clarifying letters to establish mutual agreement on contract interpretations, resolving ambiguities in active provisions.', 4),
('skills_list', 'Labor Relations & Collective Bargaining', 'Sunshine Proposals', 'Preparing, publicizing, and presenting initial bargaining packages to public bodies or management in compliance with statutory notice rules.', 5),
('skills_list', 'Labor Relations & Collective Bargaining', 'Tentative Agreements (TA)', 'Securing signed tentative agreements on individual contract articles, ensuring alignment on language details before full contract submission.', 6),
('skills_list', 'Labor Relations & Collective Bargaining', 'Federal Mediation & Conciliation Service (FMCS)', 'Utilizing federal mediation processes, filing dispute notices, and collaborating with mediators to resolve high-stakes bargaining impasses.', 7),
('skills_list', 'Labor Relations & Collective Bargaining', 'Bargaining Unit Restructuring', 'Auditing and renegotiating unit scope, classifications, and department configurations during employer reorganizations or expansions.', 8),
('skills_list', 'Labor Relations & Collective Bargaining', 'Contract Ratification', 'Managing ratification campaigns, educating members on contract changes, and coordinating vote logistics to finalize agreements.', 9),
('skills_list', 'Labor Relations & Collective Bargaining', 'Strike Sanctions & Dispute Notices', 'Filing legally required dispute notices, securing strike authorizations, and organizing strike preparation committees.', 10),
('skills_list', 'Grievance & Legal Administration', 'Multi-Level Grievance Arbitration', NULL, 11),
('skills_list', 'Grievance & Legal Administration', 'Unfair Labor Practices (ULP)', NULL, 12),
('skills_list', 'Grievance & Legal Administration', 'National Labor Relations Board (NLRB) Actions', NULL, 13),
('skills_list', 'Grievance & Legal Administration', 'Just Cause Disciplinary Defense', NULL, 14),
('skills_list', 'Grievance & Legal Administration', 'Seniority Roster Auditing', NULL, 15),
('skills_list', 'Grievance & Legal Administration', 'Back-Pay Forensic Calculations', NULL, 16),
('skills_list', 'Grievance & Legal Administration', 'Meet-and-Confer Proceedings', NULL, 17),
('skills_list', 'Grievance & Legal Administration', 'Joint Labor-Management Committees (JLMC)', NULL, 18),
('skills_list', 'Grievance & Legal Administration', 'Taft-Hartley Trust Compliance', NULL, 19),
('skills_list', 'Grievance & Legal Administration', 'Workplace Investigation Advocacy', NULL, 20),
('skills_list', 'Internal Organizing & Field Strategy', 'Total Workforce Representation (TWR) Metrics', NULL, 21),
('skills_list', 'Internal Organizing & Field Strategy', 'Worksite Unit Mapping', NULL, 22),
('skills_list', 'Internal Organizing & Field Strategy', 'On-the-Ground Strategic Organizing', NULL, 23),
('skills_list', 'Internal Organizing & Field Strategy', 'New Employee Orientation (NEO) Design', NULL, 24),
('skills_list', 'Internal Organizing & Field Strategy', 'Member Leader Recruitment', NULL, 25),
('skills_list', 'Internal Organizing & Field Strategy', 'Dues Check-Off Onboarding Systems', NULL, 26),
('skills_list', 'Internal Organizing & Field Strategy', 'Digital/Telephonic Call Center Campaigns (CallEvo/CallHub)', NULL, 27),
('skills_list', 'Internal Organizing & Field Strategy', 'Member Mobilization & Caucus Leadership', NULL, 28),
('skills_list', 'Internal Organizing & Field Strategy', 'Public-Sector Chapter Management', NULL, 29),
('skills_list', 'Internal Organizing & Field Strategy', 'Campaign Data Analytics', NULL, 30),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'Forensic Information Requests', 'Engineering exhaustive data demands targeting payroll registries, employee demographics, operational expenses, and contractor usage to prepare for negotiations.', 31),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'IRS Form 990 & Financial Auditing', 'Analyzing employer IRS Form 990 filings, balance sheets, and budget statements to evaluate fiscal health and uncover hidden reserves.', 32),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'Workplace Violence Prevention Policies', 'Reviewing, auditing, and advising on employer workplace safety and violence prevention frameworks to ensure regulatory compliance and employee security.', 33),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'OSHA Compliance & Log Auditing', 'Examining employer OSHA 300 logs, incident reports, and safety protocols to identify workplace safety violations and enforce corrective measures.', 34),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'Workers’ Compensation Claim Analysis', 'Auditing industrial injury claims, return-to-work programs, and employer insurance logs to protect injured workers and verify proper coverage.', 35),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'Classification & Retention Studies', 'Reviewing job descriptions, salary bands, and market metrics to build classification studies that support retention and equitable pay adjustments.', 36),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'Qualifying Life Event (QLE) Plan Rules', 'Advising members on health benefit enrollments, status changes, and plan document rules following marriage, birth, or coverage loss events.', 37),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'FMLA / CFRA Leave Administration', 'Representing employees requesting medical or family leave, auditing employer leave calculations, and defending against unlawful retaliation.', 38),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'Total Compensation & Wage Opener Modeling', 'Modeling economic impacts of wage increases, health contribution shifts, and retirement match changes to build comprehensive cost proposals.', 39),
('skills_list', 'Compliance, HR, & Financial Diagnostics', 'Turnover Rate Risk Assessment', 'Analyzing employee tenure, resignation patterns, and exit survey data to isolate retention risks and bargain for structural solutions.', 40),
('skills_list', 'Strategic & Global Organizing Campaigns', 'Comprehensive Corporate Campaigns', 'Using strategic pressure on an employer''s weak or vulnerable areas (analyzing social, financial, and political networks) and mobilizing community support rather than relying solely on traditional strikes.', 41),
('skills_list', 'Strategic & Global Organizing Campaigns', 'International Labor Solidarity', 'Partnering with global sister unions (such as the UK-based Unite and the International Transport Workers'' Federation) to execute "all-in" coordinated international campaigns.', 42),
('skills_list', 'Strategic & Global Organizing Campaigns', 'Strategic Direct Actions & Workplace Tactics', 'Planning and executing focused direct-action tactics, such as statewide wage-theft campaigns or community-backed sidewalk work-stoppage meetings.', 43),
('skills_list', 'Regulatory & Board Representation (NLRB & RLA)', 'Unfair Labor Practice (ULP) Enforcement', 'Identifying employer misconduct (such as unlawful retaliation or surface bargaining) and successfully filing, investigating, and litigating ULP charges with the National Labor Relations Board.', 44),
('skills_list', 'Regulatory & Board Representation (NLRB & RLA)', 'Strategic Election Interventions', 'Managing complex representation election procedures, including filing requests to block decertification petitions during active employer interference and fighting for re-run elections following labor board technical errors.', 45),
('skills_list', 'Regulatory & Board Representation (NLRB & RLA)', 'Showing of Interest & Card Checks', 'Managing and verifying authorization cards or petitions to establish clear bargaining unit majority or cross-table recognition.', 46),
('skills_list', 'Internal Union Governance & Oversight', 'Trusteeships', 'Understanding the process of appointing and acting as a temporary trustee to manage local union assets, rectify internal financial malpractices, handle independent audits, and correct administrative corruption.', 47),
('skills_list', 'Internal Union Governance & Oversight', 'Fiduciary & Compliance Monitoring', 'Utilizing independent bodies (like the Independent Review Board) to permanently bar corrupt elements, handle confidential hotline investigations, and ensure compliance with the Landrum-Griffin Act.', 48),
('skills_list', 'Workplace Advocacy & Employee Protections', 'Weingarten Rights & Interrogation Defense', 'Safeguarding workers against management coercion during critical investigatory interviews, serving as a witness, objecting to intimidation, and raising extenuating factors.', 49),
('skills_list', 'Workplace Advocacy & Employee Protections', 'External Statutory Enforcement', 'Educating and representing members on rights derived entirely outside the collective bargaining agreement, such as the Family and Medical Leave Act (FMLA), California Family Rights Act (CFRA), Workers'' Compensation, and the Americans with Disabilities Act (ADA).', 50),
('skills_list', 'Political Action & Community Engagement', 'Governmental & Legislative Affairs', 'Engaging in political activism, lobbying for key legislative initiatives, and coordinating with municipal regulatory bodies (e.g., SFMTA, Board of Supervisors) to advocate on behalf of the workforce.', 51),
('skills_list', 'Political Action & Community Engagement', 'Community Coalition Building', 'Partnering with regional organizing coalitions (e.g., Silicon Valley Rising) and local community/charitable networks to protect living standards and affordable housing initiatives.', 52);

-- Seed Technical Skills (appended to competencies)
INSERT INTO competencies (group_type, category, name, description, sort_order) VALUES
('technical_skills', 'Operating System Mastery', 'macOS & iOS', 'Extensive experience utilizing Apple products, hardware accessories, and creative applications within a professional studio environment. Highly proficient at executing localized mobile workflows and hardware configurations across the Apple ecosystem.', 1),
('technical_skills', 'Operating System Mastery', 'Windows', 'Long-term experience deploying enterprise productivity tools, campaign software, and secure application packages in Windows environments.', 2),
('technical_skills', 'Operating System Mastery', 'Android', 'Experienced with mobile operating system frameworks, including downloading, deploying, and formatting custom application packages (.apk files) to configure secure lockdown hardware tablets.', 3),
('technical_skills', 'Productivity & Collaboration Suites', 'Google Workspace (Google Suite)', 'Deep operational familiarity with Google Drive for secure asset storage, file transfer organization, and remote client collaboration. Competent utilizing Google collaboration tools to streamline production workflows.', 4),
('technical_skills', 'Productivity & Collaboration Suites', 'Microsoft 365', 'Advanced mastery across the enterprise office suite, including formatting text documents in Word, tracking metrics and logs in Excel, building client-facing presentations in PowerPoint, and managing communication channels via Outlook and Teams.', 5),
('technical_skills', 'Coding & Software Engineering Skills', 'Frontend Web Development', 'Experienced in building modern, responsive, and intuitive web applications using the React framework.', 6),
('technical_skills', 'Coding & Software Engineering Skills', 'Software Architecture', 'Skilled in designing Component Architecture to ensure modular, clean, and maintainable codebases.', 7),
('technical_skills', 'Coding & Software Engineering Skills', 'State Management & Persistence', 'Advanced capability implementing complex frontend state management to handle data flow, preserve form data, and maintain state continuity across multi-step user workflows.', 8),
('technical_skills', 'Coding & Software Engineering Skills', 'Session Management & Auto-Save Systems', 'Developed intelligent, event-driven auto-save systems and invisible session recovery logic to prevent data loss and enhance application reliability.', 9),
('technical_skills', 'Coding & Software Engineering Skills', 'Information Architecture & UX Engineering', 'Proficient at translating complex background analysis into structured multi-step wizard interfaces (e.g., Input → Analysis → Curation → Export) featuring progressive enhancement, navigation constraints, and progress tracking.', 10),
('technical_skills', 'Coding & Software Engineering Skills', 'Performance & Error Optimization', 'Focus on frontend performance optimization, technical debt mitigation, and implementing responsive design paradigms across disparate device screens.', 11),
('technical_skills', 'Coding & Software Engineering Skills', 'AI Tool Integration & Prompt Engineering', 'Familiar with leveraging Generative AI capabilities for code generation, text parsing, and building collaborative human-in-the-loop AI workflows.', 12),
('technical_skills', 'Digital Media Production & Technical Systems', 'Multi-Cam Field Switching & Live Streaming', 'Deep technical capability routing, switching, and syncing live audio and multi-camera video signals in real-time using advanced streaming engines.', 13),
('technical_skills', 'Digital Media Production & Technical Systems', 'Network & Live Transmission Engineering', 'Experienced in deploying ad-hoc Local and Wide Area Networks (WANs) utilizing Power over Ethernet (PoE) cameras to stream compressed live programming to YouTube over cellular and satellite bands under tight infrastructure or low-bandwidth constraints.', 14),
('technical_skills', 'Digital Media Production & Technical Systems', 'Video Encoding & Compression Management', 'Knowledgeable in managing video encoding parameters, troubleshooting transmission points of failure, and balancing live recording settings against available network bandwidth.', 15),
('technical_skills', 'Digital Media Production & Technical Systems', 'Full-Cycle Podcast Production & Hosting', 'Complete technical oversight of audio/video podcast development from concept to automated multi-platform distribution and promotional asset generation.', 16),
('technical_skills', 'Digital Media Production & Technical Systems', 'Studio Audio Engineering & Hardware Configuration', 'Mastery of signal routing, level management, and physical deployment for high-end studio gear including multi-microphone arrays and production mixing consoles.', 17),
('technical_skills', 'Digital Media Production & Technical Systems', 'Search Engine Optimization (SEO) & Algorithmic Strategy', 'Highly skilled in deploying web SEO configurations, analyzing audience data trends, and leveraging platform content algorithms to maximize digital visibility and engagement.', 18),
('technical_skills', 'Digital Media Production & Technical Systems', 'Paid Social Dashboard Architecture', 'Competent in structuring, targeting, and managing paid ad campaigns across native Meta, X (Twitter), and Google advertising dashboards to drive consumer actions and scale rapid audience growth.', 19),
('technical_skills', 'Digital Media Production & Technical Systems', 'Web Development & Layout Design', 'Front-end web layout configurations, system modernizations, landing page optimization, and centralized link-tree integration.', 20),
('technical_skills', 'Digital Media Production & Technical Systems', 'Digital Crisis Management & Brand Defense', 'Advanced execution of corporate digital counter-narratives using targeted ad models to redirect search traffic, combined with executing intellectual property protections such as copyright and DMCA removal requests.', 21),
('technical_skills', 'Software Proficiencies Master List - Audio, Video & Graphic Editing', 'Final Cut Pro', 'Mastered for multi-cam timeline synchronization, asset generation, and premium long- and short-form video cutting.', 22),
('technical_skills', 'Software Proficiencies Master List - Audio, Video & Graphic Editing', 'Adobe Audition', 'Primary environment for professional multi-track audio engineering, vocal cleaning, and post-production mixing.', 23),
('technical_skills', 'Software Proficiencies Master List - Audio, Video & Graphic Editing', 'Adobe Creative Suite', 'Proficient across creative asset generation software including Photoshop, Illustrator, and InDesign.', 24),
('technical_skills', 'Software Proficiencies Master List - Audio, Video & Graphic Editing', 'Descript', 'Proficient with algorithmic text-based audio parsing, track cleanup, and automated transcription editing.', 25),
('technical_skills', 'Software Proficiencies Master List - Audio, Video & Graphic Editing', 'CapCut', 'Leveraged for rapid-turnaround video asset creation, mobile cutting, and short-form video formatting.', 26),
('technical_skills', 'Software Proficiencies Master List - Audio, Video & Graphic Editing', 'Canva', 'Utilized for quick-to-market digital designs, flyers, presentation pitch decks, and visual media templates.', 27),
('technical_skills', 'Software Proficiencies Master List - Media Distribution, Streaming & Studio Capture', 'Spreaker Studio', 'Enterprise podcast syndication dashboard, audio stream delivery, and major network distribution management.', 28),
('technical_skills', 'Software Proficiencies Master List - Media Distribution, Streaming & Studio Capture', 'Riverside.fm', 'Advanced browser-based remote capture platform used to manage multi-camera isolated local recording tracks and large-scale data transfers.', 29),
('technical_skills', 'Software Proficiencies Master List - Media Distribution, Streaming & Studio Capture', 'Switcher Studio', 'Native iOS-based switching and live broadcasting suite used to sync audio mixers and cameras on field shoots.', 30),
('technical_skills', 'Software Proficiencies Master List - Media Distribution, Streaming & Studio Capture', 'Opus.pro', 'Automated AI-driven short-form video extraction, framing, and clip maximization.', 31),
('technical_skills', 'Software Proficiencies Master List - Campaign Data, Communications & Operations', 'CallEvo / CallHub', 'Deployment and operational management of digital and telephonic call center campaigns.', 32),
('technical_skills', 'Software Proficiencies Master List - Campaign Data, Communications & Operations', 'Salesforce & Native CRMs', 'Core functional knowledge of CRM system foundations and member database management tools.', 33),
('technical_skills', 'Software Proficiencies Master List - Campaign Data, Communications & Operations', 'Fillout Forms', 'Configuration of customized automated external booking widgets, custom images, and calendar scheduling flows.', 34),
('technical_skills', 'Software Proficiencies Master List - Campaign Data, Communications & Operations', 'Fully Kiosk / Single App Kiosk', 'Technical implementation of Android lockdown applications for deployment on public hardware displays.', 35);

-- ==========================================
-- JOB SKILLS & EMPLOYER MAPPING
-- Run this block to enrich the 5 labor timeline entries
-- ==========================================

-- SEIU Local 1021 — Field Representative
UPDATE timeline SET
  employer_list = '["California Academy of Sciences","Santa Rosa Junior College (SRJC)","Cotati-Rohnert Park USD","Sebastopol USD","Geyserville USD","Community Action Marin","Head Start Sonoma","City of Rohnert Park","The Exploratorium"]'::jsonb,
  job_skills = '[
    {"name": "Successor Contract Negotiations", "description": "Drafting, editing, and executing initial and renewal collective bargaining agreements."},
    {"name": "Forensic Information Requests", "description": "Engineering granular data demands for employee demographics and audited financial indicators."},
    {"name": "Worksite Unit Mapping", "description": "Conducting department-by-department layout mappings to optimize internal site mobilization."},
    {"name": "Total Workforce Representation (TWR)", "description": "Tracking member density percentiles and check-off authorization pipelines."},
    {"name": "Sunshine Proposals", "description": "Formulating and presenting introductory collective bargaining frameworks to public bodies."},
    {"name": "Classification & Retention Studies", "description": "Reviewing public sector employee tracking parameters and job title structures."}
  ]'::jsonb
WHERE id = 'seiu-1021';

-- Teamsters for Harris — Director
UPDATE timeline SET
  employer_list = '["National Campaign / Grassroots Labor Coalition"]'::jsonb,
  job_skills = '[
    {"name": "Digital/Telephonic Campaigns", "description": "Organizing large-scale outreach systems via customized call center infrastructure (CallEvo/CallHub)."},
    {"name": "Campaign Messaging & Communications", "description": "Crafting targeted, high-impact talking points and digital video scripts to engage labor audiences."},
    {"name": "Community Coalition Building", "description": "Uniting regional networks and localized caucuses to secure joint endorsements."},
    {"name": "Campaign Data Analytics", "description": "Distilling real-time qualitative metrics and worker sentiment trends."}
  ]'::jsonb
WHERE id = 'teamsters-harris';

-- Teamsters Local 853 — Business Representative / Communications
UPDATE timeline SET
  employer_list = '["Mercedes-Benz of Oakland","Bauer\u2019s Intelligent Transportation","WeDriveU, Inc.","Steeler, Inc.","Clean Harbors Environmental","Douglas Parking LLC","Valet Hospitality Service","Encore (Highland Hospital)","Zenith American Solutions","Durham School Services","Amports / DBI SF / Cherin\u2019s","Coca Cola / GCR Tires","Farmers Produce Corporation","G3 Logistics","Peninsula Parking","Compass Transportation","Wholesale Produce Transport","Sysco Fremont & Coast County Trucks","San Francisco Toyota","Golden Gate Freightliner, Inc.","Pregis, LLC","LAZ Parking (Kaiser & OAK)","Transdev / First Transit","MV Transportation","GardaWorld"]'::jsonb,
  job_skills = '[
    {"name": "NLRB Statutory Defense", "description": "Managing board representation hearings and executing strategic interventions to block decertification petitions."},
    {"name": "Unfair Labor Practice (ULP) Enforcement", "description": "Identifying management coercion and investigating/litigating formal charges with the Board."},
    {"name": "CBA Structural Integration", "description": "Auditing and harmonizing local Memorandums of Understanding (MOUs) into master templates."},
    {"name": "Seniority Roster Auditing", "description": "Reviewing payroll logs and roster metrics for quarterly shift bidding, recalls, and step advancements."},
    {"name": "Multi-Step Grievance Processing", "description": "Documenting contractual violations, conducting witness interrogations, and securing binding settlements."},
    {"name": "Taft-Hartley Trust Compliance", "description": "Policed employer contribution logs, handled delinquency financial audits, and resolved 90-day eligibility rules."}
  ]'::jsonb
WHERE id = 'teamsters-853';

-- Teamsters Local 665 — Business Representative
UPDATE timeline SET
  employer_list = '["P & S Sales, Inc.","Central Parking System / New South Parking","Serramonte Ford","Stewart Chevrolet","A&B Towing / Jenkins Towing","AutoWest Honda","Storer Transportation","ABM (Oakland International Airport)","Sims Metal Management","SFO Shuttle Bus Company","Park \u2019N Fly Service, LLC","Fregene\u2019s","SuperShuttle of San Francisco"]'::jsonb,
  job_skills = '[
    {"name": "Federal Mediation Pathways", "description": "Navigating bargaining impasses and filing formal dispute notices with the FMCS."},
    {"name": "Contract Ratification", "description": "Coordinating proposal assemblies, adjusting vacation caps, and executing final settlement terms."},
    {"name": "Bargaining Unit Restructuring", "description": "Defining employee classifications, regular hours, and operational shop-floor parameters."},
    {"name": "Just Cause Disciplinary Defense", "description": "Evaluating workplace investigations using investigative frameworks to challenge unjust discipline."},
    {"name": "Meet-and-Confer Proceedings", "description": "Directing joint labor-management committee reviews regarding route times and safety standards."}
  ]'::jsonb
WHERE id = 'teamsters-665';

-- Teamsters Local 624 — President
UPDATE timeline SET
  employer_list = '["The Press Democrat","Golden State Lumber, Inc.","Alsco / Steiner Corp.","Young\u2019s Market / Dairymen\u2019s Feed","Kings County Truck Lines","ABF Freight / Pacific Supply","Lucero Trucking / Airborne Express","Aramark Uniform Services","NeilMed Products, Inc.","DHL Express","Curtin Air Freight, Inc.","Unipart Services America, Inc.","Farmer Brothers Coffee","Buchanan Food Service","Lumber and Mill Employers Association (LAMEA)","Loop Transportation","First Student / Vella","USF Reddaway","Petaluma Poultry Processors","North Bay Corporation","Lace House Linen","Luxor Cab / Yellow Cab","Marin Sanitary Service","Mill Valley Refuse & Recycling","McPhail\u2019s Fuel Company","Yellow Freight & Roadway Freight"]'::jsonb,
  job_skills = '[
    {"name": "Executive Contract Architecture", "description": "Leading multi-party collective bargaining for renewals, master extensions, and wage openers."},
    {"name": "On-the-Ground Strategic Organizing", "description": "Spearheading environments blueprints, card check verifications, and winning NLRB elections."},
    {"name": "Economic Package Modeling", "description": "Architecting complex wage scales, COLAs, health/welfare contribution models, and retirement plans."},
    {"name": "Strike Sanctions & Dispute Management", "description": "Organizing legal strike votes, issuing formal notices, and directing field tactics."},
    {"name": "Effects-of-Closure Bargaining", "description": "Navigating transition strategies and trust fund compliance during operational cessations."},
    {"name": "Internal Union Governance", "description": "Managing assets, handling independent audits, and ensuring strict Landrum-Griffin Act compliance."}
  ]'::jsonb
WHERE id = 'teamsters-624';

-- ==========================================
-- DIGITAL JOB SKILLS MAPPING
-- Run this block to enrich the 5 digital/creative timeline entries
-- ==========================================

-- Redwood Empire Media — Partner / Producer / Editor
UPDATE timeline SET
  job_skills = '[
    {"name": "Video Production", "description": ""},
    {"name": "Podcast Production", "description": ""},
    {"name": "Audio Engineering", "description": ""},
    {"name": "Studio Production", "description": ""},
    {"name": "Live Streaming", "description": ""},
    {"name": "Content Strategy", "description": ""},
    {"name": "Brand Identity", "description": ""},
    {"name": "Social Media", "description": ""}
  ]'::jsonb
WHERE id = 'redwood-empire';

-- Apple — Specialist
UPDATE timeline SET
  job_skills = '[
    {"name": "Brand Identity", "description": ""},
    {"name": "Content Strategy", "description": ""},
    {"name": "Social Media", "description": ""},
    {"name": "AI & Automation", "description": ""}
  ]'::jsonb
WHERE id = 'apple';

-- NorCal Pods — Content Producer
UPDATE timeline SET
  job_skills = '[
    {"name": "Podcast Production", "description": ""},
    {"name": "Audio Engineering", "description": ""},
    {"name": "Content Strategy", "description": ""},
    {"name": "Brand Identity", "description": ""},
    {"name": "SEO & Web Dev", "description": ""},
    {"name": "Social Media", "description": ""}
  ]'::jsonb
WHERE id = 'norcal-pods';

-- Freelance — Independent Communications Consultant
UPDATE timeline SET
  job_skills = '[
    {"name": "Live Streaming", "description": ""},
    {"name": "Video Production", "description": ""},
    {"name": "Graphic Design", "description": ""},
    {"name": "Content Strategy", "description": ""},
    {"name": "Brand Identity", "description": ""},
    {"name": "Social Media", "description": ""},
    {"name": "SEO & Web Dev", "description": ""},
    {"name": "Email Marketing", "description": ""}
  ]'::jsonb
WHERE id = 'freelance';

-- Healthy Democracy — Technology and Logistics Specialist
UPDATE timeline SET
  job_skills = '[
    {"name": "Live Streaming", "description": ""},
    {"name": "Studio Production", "description": ""},
    {"name": "Video Production", "description": ""},
    {"name": "Audio Engineering", "description": ""},
    {"name": "Coding & Dev", "description": ""}
  ]'::jsonb
WHERE id = 'healthy-democracy';

-- ==========================================
-- MEDIA LIBRARY TABLES
-- ==========================================

-- Media Assets table (uploaded files with metadata)
CREATE TABLE IF NOT EXISTS media_assets (
  id              UUID        DEFAULT gen_random_uuid() PRIMARY KEY,
  created_at      TIMESTAMPTZ DEFAULT now(),
  timeline_job_id TEXT        REFERENCES timeline(id) ON DELETE SET NULL,
  filename        TEXT        NOT NULL,
  storage_path    TEXT        NOT NULL,
  public_url      TEXT        NOT NULL,
  file_type       TEXT        NOT NULL CHECK (file_type IN ('image','video','pdf')),
  mime_type       TEXT,
  caption         TEXT,
  keywords        TEXT[]      DEFAULT '{}',
  location_label  TEXT,
  location_lat    DECIMAL(9,6),
  location_lng    DECIMAL(9,6),
  sort_order      INTEGER     DEFAULT 0
);

ALTER TABLE media_assets ENABLE ROW LEVEL SECURITY;
CREATE POLICY "media_assets_public_read"   ON media_assets FOR SELECT USING (true);
CREATE POLICY "media_assets_service_write" ON media_assets FOR ALL   USING (auth.role() = 'service_role');

-- App Settings table (key/value config, used for admin password hash)
CREATE TABLE IF NOT EXISTS app_settings (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);

ALTER TABLE app_settings ENABLE ROW LEVEL SECURITY;
CREATE POLICY "app_settings_no_public_read" ON app_settings FOR SELECT USING (false);
CREATE POLICY "app_settings_service_only"   ON app_settings FOR ALL   USING (auth.role() = 'service_role');

-- Slides table (carousel slides)
CREATE TABLE IF NOT EXISTS slides (
  id           SERIAL PRIMARY KEY,
  title        TEXT NOT NULL,
  content_type TEXT NOT NULL,
  content_data JSONB NOT NULL,
  is_enabled   BOOLEAN DEFAULT true,
  sort_order   INTEGER DEFAULT 0
);

ALTER TABLE slides ENABLE ROW LEVEL SECURITY;
CREATE POLICY "slides_public_read"   ON slides FOR SELECT USING (true);
CREATE POLICY "slides_service_only"  ON slides FOR ALL   USING (auth.role() = 'service_role');

-- Seed slides
TRUNCATE slides RESTART IDENTITY CASCADE;
INSERT INTO slides (title, content_type, content_data, is_enabled, sort_order) VALUES
('Professional Summary', 'markdown', '{
  "lead": "Combines deep labor relations expertise with cutting-edge digital communication strategies to amplify voices, build coalitions, and advance economic and social justice.",
  "body": "Field representative, former union local president, and digital media producer with 20+ years of experience directing high-impact contract campaigns, building nationwide labor coalitions, and producing viral video/audio podcasts."
}'::jsonb, true, 1),
('Personal Highlights', 'personal_timeline', '[
  {"year": 1969, "title": "Born", "details": "Born and raised in California, starting a lifelong journey."},
  {"year": 1988, "title": "Academic Foundations", "details": "Began coursework in Political Science and Economics at Santa Rosa Junior College."},
  {"year": 1989, "title": "Cuesta College Studies", "details": "Continued studies in economics and community advocacy."},
  {"year": 1999, "title": "Entered Labor Leadership", "details": "Dedicated focus to organizing campaigns and worker advocacy."},
  {"year": 2006, "title": "Local Union Leadership", "details": "Elected President of Teamsters Local 624, leading strategic contract campaigns."},
  {"year": 2026, "title": "Present Day", "details": "Integrating full-stack web development with senior labor organizing."}
]'::jsonb, true, 2);

-- Seed Testimonials
TRUNCATE testimonials RESTART IDENTITY CASCADE;
INSERT INTO testimonials (name, title, company, content, linkedin_url, sort_order) VALUES
('Jane Doe', 'Senior Organizer', 'SEIU 1021', 'Phil is an incredible leader who knows how to build consensus and drive real results. Working with him was a masterclass in strategic communications.', 'https://linkedin.com', 1),
('John Smith', 'Communications Director', 'Teamsters', 'I have never met someone so capable of blending traditional labor organizing with modern digital campaigns. Highly recommended.', 'https://linkedin.com', 2);

-- ==========================================
-- 8. RESUME VARIANTS EXTENSION
-- ==========================================

-- Tagging skills with variants (for filtering or reordering)
ALTER TABLE skills ADD COLUMN IF NOT EXISTS variants text[] DEFAULT '{standard, aaup, labor}';

-- Tagging competencies with variants
ALTER TABLE competencies ADD COLUMN IF NOT EXISTS variants text[] DEFAULT '{standard, aaup, labor}';

-- Insert variant slogan overrides in app_settings
INSERT INTO app_settings (key, value) VALUES
('slogan_aaup', 'Digital Media Producer & Labor Communications Strategist'),
('slogan_labor', 'Senior Labor Relations Representative & Collective Bargaining Expert')
ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;