33 lines
1.5 KiB
SQL
33 lines
1.5 KiB
SQL
-- Migration to support multi-election and enhanced candidate management
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
-- Create parties table for definition
|
|
CREATE TABLE IF NOT EXISTS parties (
|
|
id CHAR(36) PRIMARY KEY,
|
|
election_id CHAR(36) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
logo_url VARCHAR(255),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Add election_id to audit_logs if not exists
|
|
-- Check if column exists is not directly possible in standard SQL without procedural, but we can try to add it.
|
|
-- Since this is a fresh migration for polishing, we assume it's okay.
|
|
ALTER TABLE audit_logs ADD COLUMN election_id CHAR(36) NULL;
|
|
ALTER TABLE audit_logs ADD CONSTRAINT fk_audit_election FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE;
|
|
|
|
-- Ensure election_assignments is used correctly
|
|
-- We don't need to change the schema here, but we will update the logic.
|
|
|
|
-- Add some sample parties for the existing elections
|
|
INSERT INTO parties (id, election_id, name, description)
|
|
SELECT UUID(), id, 'PROGRESSIVE PARTY', 'Committed to innovation and change.' FROM elections;
|
|
INSERT INTO parties (id, election_id, name, description)
|
|
SELECT UUID(), id, 'UNITY ALLIANCE', 'Together for a better future.' FROM elections;
|
|
INSERT INTO parties (id, election_id, name, description)
|
|
SELECT UUID(), id, 'YOUTH VOICE', 'Empowering the next generation.' FROM elections;
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|