-- Seed data for TradesIreland.ie MVP
SET NAMES utf8mb4;

-- Default admin: email admin@tradesireland.ie / password: password  (CHANGE IMMEDIATELY)
INSERT INTO admins (email, password_hash) VALUES
('admin@tradesireland.ie', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

INSERT INTO counties (name, slug, seo_title, meta_description, intro_text) VALUES
('Carlow', 'carlow', 'Tradespeople in Carlow | Trades Ireland', 'Find vetted plumbers, electricians, builders and more across County Carlow.', 'Whether you are renovating in Carlow town or upgrading a home in the county, browse local tradespeople by category.'),
('Cavan', 'cavan', 'Tradespeople in Cavan | Trades Ireland', 'Directory of trades and home services across County Cavan.', 'From bathroom upgrades to garden projects, connect with tradespeople working across Cavan.'),
('Clare', 'clare', 'Tradespeople in Clare | Trades Ireland', 'Find trades in Ennis, Shannon and across County Clare.', 'County Clare homeowners use Trades Ireland to compare local businesses and get work done with confidence.'),
('Cork', 'cork', 'Tradespeople in Cork | Trades Ireland', 'Find plumbers, electricians, builders and more in Cork city and county.', 'Cork is one of Ireland’s busiest counties for home improvements. Start with your trade category and shortlist local pros.'),
('Donegal', 'donegal', 'Tradespeople in Donegal | Trades Ireland', 'Local trades directory for Donegal — Letterkenny to the coast.', 'Browse Donegal tradespeople by category. Ideal for renovations, repairs and new builds.'),
('Dublin', 'dublin', 'Tradespeople in Dublin | Trades Ireland', 'Find trusted trades in Dublin — north, south, city and suburbs.', 'Dublin homeowners move fast. Use this hub to explore trade categories and featured businesses across the county.'),
('Galway', 'galway', 'Tradespeople in Galway | Trades Ireland', 'Galway city and county trades directory for home and commercial work.', 'From city apartments to rural homes, find Galway tradespeople organised by trade type.'),
('Kerry', 'kerry', 'Tradespeople in Kerry | Trades Ireland', 'Trades and home services across County Kerry.', 'Browse Kerry listings for maintenance, renovations and specialist trades.'),
('Kildare', 'kildare', 'Tradespeople in Kildare | Trades Ireland', 'Naas, Newbridge, Maynooth and county-wide trades directory.', 'Kildare’s commuter towns need reliable trades — start your search by category.'),
('Kilkenny', 'kilkenny', 'Tradespeople in Kilkenny | Trades Ireland', 'Find local trades in Kilkenny city and surrounds.', 'Heritage homes and new builds alike — browse Kilkenny trades by specialism.'),
('Laois', 'laois', 'Tradespeople in Laois | Trades Ireland', 'Portlaoise and County Laois trades directory.', 'Compare local tradespeople for upgrades, repairs and commercial fit-outs across Laois.'),
('Leitrim', 'leitrim', 'Tradespeople in Leitrim | Trades Ireland', 'Local directory for trades across County Leitrim.', 'Smaller projects still need the right pro — browse Leitrim businesses by trade.'),
('Limerick', 'limerick', 'Tradespeople in Limerick | Trades Ireland', 'City and county Limerick trades for homeowners and businesses.', 'Limerick’s housing stock spans eras — find specialists from heating to roofing.'),
('Longford', 'longford', 'Tradespeople in Longford | Trades Ireland', 'County Longford trades and home services.', 'Local businesses listed by trade — ideal for extensions, heating and electrical work.'),
('Louth', 'louth', 'Tradespeople in Louth | Trades Ireland', 'Dundalk, Drogheda and County Louth trades.', 'North-east Ireland’s busy corridor — browse Louth trades by category.'),
('Mayo', 'mayo', 'Tradespeople in Mayo | Trades Ireland', 'Castlebar, Westport and county-wide trades directory.', 'Mayo properties face Atlantic weather — find roofers, builders and more locally.'),
('Meath', 'meath', 'Tradespeople in Meath | Trades Ireland', 'Navan, Trim, Ashbourne and Meath-wide listings.', 'One of Ireland’s fastest-growing counties — find trades for new homes and renovations.'),
('Monaghan', 'monaghan', 'Tradespeople in Monaghan | Trades Ireland', 'County Monaghan trades directory.', 'Browse Monaghan businesses for indoor and outdoor home projects.'),
('Offaly', 'offaly', 'Tradespeople in Offaly | Trades Ireland', 'Tullamore and County Offaly home services.', 'Find tradespeople for upgrades and repairs across Offaly.'),
('Roscommon', 'roscommon', 'Tradespeople in Roscommon | Trades Ireland', 'Local trades across County Roscommon.', 'From heating upgrades to landscaping — explore Roscommon listings.'),
('Sligo', 'sligo', 'Tradespeople in Sligo | Trades Ireland', 'Sligo town and county trades directory.', 'Browse Sligo trades by category for residential and small commercial work.'),
('Tipperary', 'tipperary', 'Tradespeople in Tipperary | Trades Ireland', 'North and South Tipperary trades for every home project.', 'Large county, varied housing — shortlist Tipperary pros by trade.'),
('Waterford', 'waterford', 'Tradespeople in Waterford | Trades Ireland', 'City and county Waterford trades directory.', 'Coastal conditions and older stock — find specialists across Waterford.'),
('Westmeath', 'westmeath', 'Tradespeople in Westmeath | Trades Ireland', 'Athlone, Mullingar and county-wide trades.', 'Westmeath homeowners compare local listings by trade type.'),
('Wexford', 'wexford', 'Tradespeople in Wexford | Trades Ireland', 'Wexford town, Gorey and county listings.', 'Sea air and seasonal maintenance — browse Wexford tradespeople.'),
('Wicklow', 'wicklow', 'Tradespeople in Wicklow | Trades Ireland', 'Bray, Greystones, Wicklow town and the Garden County.', 'Wicklow’s mix of coastal and mountain homes needs reliable trades — start here.');

INSERT INTO trade_types (name, slug, seo_title, meta_description) VALUES
('Plumbers', 'plumbers', 'Plumbers in Ireland | Trades Ireland', 'Find qualified plumbers for repairs, bathrooms, heating and more.'),
('Electricians', 'electricians', 'Electricians in Ireland | Trades Ireland', 'Domestic and commercial electricians — rewires, EV chargers, testing.'),
('Builders', 'builders', 'Builders in Ireland | Trades Ireland', 'Extensions, renovations and general building contractors.'),
('Roofers', 'roofers', 'Roofers in Ireland | Trades Ireland', 'Roof repairs, flat roofs, slate and tile specialists.'),
('Carpenters', 'carpenters', 'Carpenters in Ireland | Trades Ireland', 'Joinery, fitted storage, doors and second-fix carpentry.'),
('Painters', 'painters', 'Painters in Ireland | Trades Ireland', 'Interior and exterior painting and decorating.'),
('Tilers', 'tilers', 'Tilers in Ireland | Trades Ireland', 'Wall and floor tiling for kitchens, bathrooms and wet rooms.'),
('Plasterers', 'plasterers', 'Plasterers in Ireland | Trades Ireland', 'Skimming, dry lining and traditional plastering.'),
('Heating Engineers', 'heating-engineers', 'Heating engineers in Ireland | Trades Ireland', 'Boilers, heat pumps, radiators and system upgrades.'),
('Landscapers', 'landscapers', 'Landscapers in Ireland | Trades Ireland', 'Gardens, paving, drainage and outdoor projects.');

-- County + trade SEO examples (Dublin + Cork focus)
INSERT INTO county_trade_pages (county_id, trade_type_id, seo_title, meta_description, intro_text, custom_content)
SELECT c.id, t.id,
  CONCAT(t.name, ' in ', c.name, ' | Trades Ireland'),
  CONCAT('Compare ', LOWER(t.name), ' across ', c.name, '. Featured and standard listings with contact details.'),
  CONCAT('Looking for ', LOWER(t.name), ' in ', c.name, '? Browse active listings below. Featured businesses are highlighted.'),
  NULL
FROM counties c
CROSS JOIN trade_types t
WHERE (c.slug = 'dublin' AND t.slug IN ('plumbers','electricians','builders'))
   OR (c.slug = 'cork' AND t.slug IN ('plumbers','roofers'));

INSERT INTO listings (business_name, slug, county_id, trade_type_id, town_area, phone, email, website_url, short_description, full_description, services_offered, is_featured, featured_rank, sort_order, status)
SELECT 'Dublin Flow Plumbing', 'dublin-flow-plumbing', c.id, t.id, 'Rathmines', '+353 1 555 0101', 'hello@dublinflow.example', 'https://example.com/dublinflow',
  'Emergency and scheduled plumbing across Dublin.',
  'Dublin Flow Plumbing supports homeowners and small businesses with reliable plumbing, from leaks to full bathroom installs.',
  'Emergency callouts; Bathroom fitting; Heating repairs; Power flushing',
  1, 10, 0, 'active'
FROM counties c JOIN trade_types t ON c.slug='dublin' AND t.slug='plumbers';

INSERT INTO listings (business_name, slug, county_id, trade_type_id, town_area, phone, email, website_url, short_description, full_description, services_offered, is_featured, featured_rank, sort_order, status)
SELECT 'Southside Spark Ltd', 'southside-spark-dublin', c.id, t.id, 'Dún Laoghaire', '+353 1 555 0202', 'jobs@southsidespark.example', NULL,
  'Registered electricians for rewires, testing and smart upgrades.',
  'Southside Spark focuses on safe, certifiable electrical work with clear quotes and tidy finishes.',
  'Periodic inspection; Rewires; EV charger install; Kitchen circuits',
  1, 20, 0, 'active'
FROM counties c JOIN trade_types t ON c.slug='dublin' AND t.slug='electricians';

INSERT INTO listings (business_name, slug, county_id, trade_type_id, town_area, phone, email, website_url, short_description, full_description, services_offered, is_featured, featured_rank, sort_order, status)
SELECT 'Cork Build Collective', 'cork-build-collective', c.id, t.id, 'Ballincollig', '+353 21 555 0303', 'team@corkbuild.example', 'https://example.com/corkbuild',
  'Extensions and renovations with clear timelines.',
  'Cork Build Collective coordinates trades and keeps communication straightforward on mid-size residential projects.',
  'Extensions; Renovations; Kitchen structural openings; Project coordination',
  1, 15, 0, 'active'
FROM counties c JOIN trade_types t ON c.slug='cork' AND t.slug='builders';

INSERT INTO listings (business_name, slug, county_id, trade_type_id, town_area, phone, email, website_url, short_description, full_description, services_offered, is_featured, featured_rank, sort_order, status)
SELECT 'Atlantic Roof Care', 'atlantic-roof-care-galway', c.id, t.id, 'Galway City', '+353 91 555 0404', 'info@atlanticroof.example', NULL,
  'Slates, tiles and flat roof repairs across Galway.',
  'Atlantic Roof Care responds quickly to storm damage and planned replacements, with photos and written scopes.',
  'Storm repairs; Re-roofing; Flat roofs; Velux installs',
  0, 100, 5, 'active'
FROM counties c JOIN trade_types t ON c.slug='galway' AND t.slug='roofers';

INSERT INTO listings (business_name, slug, county_id, trade_type_id, town_area, phone, email, website_url, short_description, full_description, services_offered, is_featured, featured_rank, sort_order, status)
SELECT 'Limerick Heat Solutions', 'limerick-heat-solutions', c.id, t.id, 'Castletroy', '+353 61 555 0505', 'service@limheat.example', 'https://example.com/limheat',
  'Boiler swaps, heat pumps and radiator upgrades.',
  'Limerick Heat Solutions helps you choose efficient heating options suited to your home and budget.',
  'Boiler replacement; Heat pump survey; Power flushing; Controls',
  1, 30, 0, 'active'
FROM counties c JOIN trade_types t ON c.slug='limerick' AND t.slug='heating-engineers';

INSERT INTO listings (business_name, slug, county_id, trade_type_id, town_area, phone, email, website_url, short_description, full_description, services_offered, is_featured, featured_rank, sort_order, status)
SELECT 'Wicklow Garden Studio', 'wicklow-garden-studio', c.id, t.id, 'Greystones', '+353 1 555 0606', 'studio@wicklowgarden.example', NULL,
  'Design-led landscaping for Wicklow homes.',
  'Wicklow Garden Studio blends practical drainage with planting schemes that suit coastal exposure.',
  'Patios; Planting; Drainage; Maintenance plans',
  0, 100, 10, 'active'
FROM counties c JOIN trade_types t ON c.slug='wicklow' AND t.slug='landscapers';

INSERT INTO listings (business_name, slug, county_id, trade_type_id, town_area, phone, email, website_url, short_description, full_description, services_offered, is_featured, featured_rank, sort_order, status)
SELECT 'Kilkenny Fine Finish', 'kilkenny-fine-finish', c.id, t.id, 'Kilkenny City', '+353 56 555 0707', 'paint@kkfinish.example', NULL,
  'Interior painting for period and modern homes.',
  'Kilkenny Fine Finish prepares surfaces properly and uses durable coatings suited to busy family spaces.',
  'Interior painting; Feature walls; Woodwork; Colour advice',
  0, 100, 0, 'active'
FROM counties c JOIN trade_types t ON c.slug='kilkenny' AND t.slug='painters';

INSERT INTO listings (business_name, slug, county_id, trade_type_id, town_area, phone, email, website_url, short_description, full_description, services_offered, is_featured, featured_rank, sort_order, status)
SELECT 'Meath Master Tiling', 'meath-master-tiling', c.id, t.id, 'Navan', '+353 46 555 0808', 'tiling@meathmaster.example', 'https://example.com/meathtiling',
  'Large-format tiling and wet-room tanking.',
  'Meath Master Tiling delivers crisp layouts and reliable waterproofing for busy bathrooms.',
  'Wet rooms; Underfloor heating prep; Natural stone; Repairs',
  0, 100, 0, 'draft'
FROM counties c JOIN trade_types t ON c.slug='meath' AND t.slug='tilers';

INSERT INTO listings (business_name, slug, county_id, trade_type_id, town_area, phone, email, website_url, short_description, full_description, services_offered, is_featured, featured_rank, sort_order, status)
SELECT 'Cork Pipe & Drain', 'cork-pipe-drain', c.id, t.id, 'Cork City', '+353 21 555 0909', 'help@corkpipe.example', NULL,
  'Drain cleaning and plumbing maintenance.',
  'Cork Pipe & Drain clears blockages and advises on longer-term fixes to prevent repeat issues.',
  'Drain jetting; CCTV surveys; Maintenance plans',
  0, 100, 0, 'active'
FROM counties c JOIN trade_types t ON c.slug='cork' AND t.slug='plumbers';

INSERT INTO blog_posts (title, slug, excerpt, content, meta_title, meta_description, status, published_at) VALUES
('Cost to rewire a house in Ireland: what to budget in 2026',
 'cost-to-rewire-a-house-in-ireland',
 'Rewiring is a major project. Here is how Irish homeowners typically think about scope, certification and ballpark costs.',
 '<p>Rewiring costs vary with house size, access, and how much of the installation is upgraded versus replaced. In Ireland, homeowners usually budget for materials, labour, and certification.</p><p>Start by defining the scope: full rewire versus partial circuits, consumer unit upgrade, and any smart-home additions. Always use a registered electrician and retain documentation for insurers and future sales.</p><p>For directory help, explore <a href="/dublin/electricians">electricians in Dublin</a> or browse <a href="/cork">Cork trades</a> to shortlist local businesses.</p>',
 'Cost to rewire a house in Ireland | Trades Ireland',
 'Budgeting for a house rewire in Ireland: scope, certification, and what affects price.',
 'published', NOW() - INTERVAL 5 DAY),
('Best time to replace a roof in Ireland',
 'best-time-to-replace-a-roof-in-ireland',
 'Weather windows, lead times and planning ahead for roof replacements.',
 '<p>Spring and early summer often suit external roof work, but good contractors plan around Irish weather year-round. The best time is when you have time to compare quotes and schedule materials.</p><p>Look for detailed written scopes, photos of similar work, and clarity on scaffolding. If you are in <a href="/galway">Galway</a>, review <a href="/galway/roofers">roofers in Galway</a> to see active listings.</p>',
 'Best time to replace a roof in Ireland | Trades Ireland',
 'Plan roof replacement with realistic weather expectations and lead times.',
 'published', NOW() - INTERVAL 12 DAY),
('Average plumber rates in Ireland: how quotes differ',
 'average-plumber-rates-in-ireland',
 'Why two plumber quotes can look different — callouts, materials, and travel.',
 '<p>Rates depend on call-out structure, hourly versus job price, and whether parts are supplied. Travel time across counties can also affect quotes.</p><p>Use Trades Ireland to compare businesses in your area, starting from <a href="/dublin/plumbers">plumbers in Dublin</a> or your local county hub.</p>',
 'Average plumber rates in Ireland | Trades Ireland',
 'Understand plumber pricing: call-outs, materials and travel.',
 'published', NOW() - INTERVAL 20 DAY),
('Grants for insulation in Ireland: a homeowner starter guide',
 'grants-for-insulation-in-ireland',
 'High-level overview of insulation upgrade pathways — verify eligibility with official sources.',
 '<p>Grant schemes change. Always confirm current rules on official government websites before you budget.</p><p>Insulation works best as part of a broader plan: ventilation, airtightness, and heating system compatibility. Browse <a href="/blog">more guides</a> and local <a href="/dublin/builders">builders in Dublin</a> for larger retrofits.</p>',
 'Grants for insulation in Ireland | Trades Ireland',
 'Starter notes on insulation upgrades — confirm eligibility on official sites.',
 'published', NOW() - INTERVAL 28 DAY),
('Extension planning tips for Irish homeowners',
 'extension-planning-tips-ireland',
 'Feasibility, surveys, and the value of clear drawings before you price trades.',
 '<p>Extensions succeed when scope is clear: structural implications, drainage, and how you will live in the house during works.</p><p>When you are ready to shortlist contractors, browse <a href="/cork/builders">builders in Cork</a> or your county hub and compare multiple businesses.</p>',
 'Extension planning tips for Irish homeowners | Trades Ireland',
 'Practical planning tips before pricing an extension in Ireland.',
 'published', NOW() - INTERVAL 35 DAY);

INSERT INTO ad_placements (placement_key, title, image_path, target_url, is_active, start_date, end_date) VALUES
('homepage_banner', 'Advertise on Trades Ireland', NULL, '/advertise', 1, NULL, NULL),
('county_banner', 'County sponsorship slot', NULL, '/advertise', 1, NULL, NULL),
('category_banner', 'Category sponsorship slot', NULL, '/advertise', 1, NULL, NULL),
('sidebar_promo', 'Featured placement', NULL, '/advertise', 1, NULL, NULL);