--
-- Skrypt migracyjny do adaptacji serwisu dla nowej galerii zdjęć
-- (c) Programistyk 2018, pedobear mnie nie powstrzyma!
--

ALTER TABLE file ADD COLUMN imxs INTEGER DEFAULT NULL;
ALTER TABLE file ADD COLUMN imys INTEGER DEFAULT NULL;
ALTER TABLE expedition_reports ADD COLUMN photo_top INTEGER REFERENCES "file" ("id") ON DELETE SET NULL;
ALTER TABLE expedition_reports ADD COLUMN sort_order INTEGER;
CREATE INDEX expedition_reports_order ON expedition_reports (sort_order ASC NULLS LAST);
ALTER TABLE expedition ADD COLUMN og_title VARCHAR(256) DEFAULT NULL;
ALTER TABLE expedition ADD COLUMN og_description VARCHAR(512) DEFAULT NULL;
ALTER TABLE expedition ADD COLUMN og_image INTEGER REFERENCES "file" ("id") ON DELETE SET NULL;

COMMENT ON COLUMN file.imxs IS 'Szerokość obrazu, w pikselach';
COMMENT ON COLUMN file.imys IS 'Wysokość obrazu, w pikselach';
COMMENT ON COLUMN expedition_reports.sort_order IS 'Kolejność sortowania relacji';
COMMENT ON COLUMN expedition.og_title IS 'Dedykowany tytuł dla OpenGraph';
COMMENT ON COLUMN expedition.og_description IS 'Dedykowany opis dla OpenGraph';
COMMENT ON COLUMN expedition.og_image IS 'Dedykowany obraz dla OpenGraph';

-- Nadanie aktualnego sortowania w nowej kolumnie
UPDATE expedition_reports dst SET sort_order = src.row_number
FROM (
  SELECT expedition_reports.id, ROW_NUMBER() OVER (ORDER BY expedition.ord, expedition.title) FROM expedition_reports
  JOIN expedition ON expedition_reports.expedition_id = expedition.id
  WHERE expedition.gallery_id IS NOT NULL AND expedition_reports.show_button
  ORDER BY expedition.ord, expedition.title
) AS src
WHERE dst.id = src.id;

-- Kontrola:
-- SELECT * FROM expedition_reports ORDER BY sort_order;