-- Run once on existing databases (after initial schema.sql)

CREATE TABLE IF NOT EXISTS footer_section_links (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  section_id INT UNSIGNED NOT NULL,
  label VARCHAR(255) NOT NULL,
  url VARCHAR(500) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  CONSTRAINT fk_footer_link_section FOREIGN KEY (section_id) REFERENCES footer_sections(id) ON DELETE CASCADE,
  INDEX idx_footer_link_section (section_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Allow sections with links only (no HTML content required)
ALTER TABLE footer_sections MODIFY content TEXT NOT NULL DEFAULT '';

-- Migrate old flat footer nav links into a "Legal" section (English only, safe to re-run)
INSERT INTO footer_sections (locale, title, content, sort_order, enabled)
SELECT 'en', 'Legal', '', 10, 1
FROM DUAL
WHERE EXISTS (
  SELECT 1 FROM navigation_items WHERE locale = 'en' AND location = 'footer' LIMIT 1
)
AND NOT EXISTS (
  SELECT 1 FROM footer_sections WHERE locale = 'en' AND title = 'Legal' LIMIT 1
);

INSERT INTO footer_section_links (section_id, label, url, sort_order)
SELECT fs.id, ni.label, ni.url, ni.sort_order
FROM navigation_items ni
JOIN footer_sections fs ON fs.locale = ni.locale AND fs.title = 'Legal'
WHERE ni.location = 'footer' AND ni.locale = 'en'
AND NOT EXISTS (
  SELECT 1 FROM footer_section_links fsl
  WHERE fsl.section_id = fs.id AND fsl.url = ni.url LIMIT 1
);

DELETE FROM navigation_items WHERE location = 'footer';