-- Migration: Update scenes table to make contractId optional and add facility fields
-- Date: 2025-01-11
-- Description: Break the relation between contracts and scenes by making contractId optional
--              and adding facility fields directly to scenes table

-- Add new columns to scenes table
ALTER TABLE scenes 
ADD COLUMN facilityName VARCHAR(200) NOT NULL DEFAULT '',
ADD COLUMN facilityOwnerName VARCHAR(100) NOT NULL DEFAULT '',
ADD COLUMN ownerNationalID VARCHAR(20) NOT NULL DEFAULT '',
ADD COLUMN facilityLocation TEXT NOT NULL,
ADD COLUMN internalCameras INT NOT NULL DEFAULT 0,
ADD COLUMN externalCameras INT NOT NULL DEFAULT 0;

-- Make contractId nullable
ALTER TABLE scenes 
MODIFY COLUMN contractId INT NULL;

-- Update foreign key constraint to SET NULL on delete instead of CASCADE
ALTER TABLE scenes 
DROP FOREIGN KEY scenes_ibfk_1;

ALTER TABLE scenes 
ADD CONSTRAINT scenes_ibfk_1 
FOREIGN KEY (contractId) REFERENCES contracts(id) 
ON DELETE SET NULL ON UPDATE CASCADE;

-- Add indexes for new fields
CREATE INDEX idx_scenes_facilityName ON scenes(facilityName);
CREATE INDEX idx_scenes_facilityOwnerName ON scenes(facilityOwnerName);
CREATE INDEX idx_scenes_ownerNationalID ON scenes(ownerNationalID);

-- Populate new fields from existing contract data for scenes that have contractId
UPDATE scenes s
JOIN contracts c ON s.contractId = c.id
SET 
    s.facilityName = c.secondParty,
    s.facilityOwnerName = c.ownerName,
    s.ownerNationalID = c.ownerNationalID,
    s.facilityLocation = c.location,
    s.internalCameras = c.innerCamerasNumber,
    s.externalCameras = c.outerCamerasNumber
WHERE s.contractId IS NOT NULL;

-- Note: After running this migration, you should update your application code
-- to handle the new structure where scenes can exist without contracts
