-- Create competitions table
CREATE TABLE IF NOT EXISTS `competitions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `location` varchar(255) NOT NULL,
  `event_date` date NOT NULL,
  `image` varchar(255) DEFAULT NULL,
  `active` tinyint(1) DEFAULT 1,
  `display` tinyint(1) DEFAULT 1,
  `display_order` int(11) DEFAULT 0,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Create events table (if not exists)
CREATE TABLE IF NOT EXISTS `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `active` tinyint(1) DEFAULT 1,
  `display` tinyint(1) DEFAULT 1,
  `display_order` int(11) DEFAULT 0,
  `competition_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `competition_id` (`competition_id`),
  CONSTRAINT `events_ibfk_1` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert test competitions with images
INSERT INTO `competitions` (`title`, `description`, `location`, `event_date`, `image`, `active`, `display`, `display_order`) VALUES
('City Photo Fest 2025', 'Annual urban photography festival showcasing the best cityscapes and street photography', 'New York', '2025-07-14', 'DWC_CT_2025.png', 1, 1, 1),
('Weddings in Focus', 'Professional wedding photography competition and showcase', 'Los Angeles', '2025-06-30', 'DWC_PTA_2025.png', 1, 1, 2),
('Nature Lens Awards', 'Wildlife and nature photography competition', 'Denver', '2025-05-22', 'SADSC_PTA_2025.png', 1, 1, 3),
('Street Snap Challenge', 'Street photography competition for emerging photographers', 'Chicago', '2025-04-15', 'DWC_CT_2025.png', 1, 1, 4),
('Portrait Masters', 'Portrait photography competition and workshop series', 'Miami', '2025-08-20', 'DWC_PTA_2025.png', 1, 1, 5),
('Architecture Through Lens', 'Architectural photography competition', 'San Francisco', '2025-09-10', 'SADSC_PTA_2025.png', 1, 1, 6);

-- Insert test events
INSERT INTO `events` (`code`, `title`, `description`, `active`, `display`, `display_order`, `competition_id`) VALUES
('CPF001', 'Urban Landscapes', 'Capture the essence of city life through architectural photography', 1, 1, 1, 1),
('CPF002', 'Street Portraits', 'Document the diverse faces of urban communities', 1, 1, 2, 1),
('WIF001', 'Wedding Ceremonies', 'Traditional and modern wedding ceremony photography', 1, 1, 1, 2),
('WIF002', 'Reception Coverage', 'Wedding reception and celebration photography', 1, 1, 2, 2),
('NLA001', 'Wildlife Photography', 'Capture animals in their natural habitats', 1, 1, 1, 3),
('NLA002', 'Landscape Photography', 'Natural landscapes and scenic photography', 1, 1, 2, 3),
('SSC001', 'Street Life', 'Document daily life in urban environments', 1, 1, 1, 4),
('SSC002', 'Urban Culture', 'Cultural events and street performances', 1, 1, 2, 4),
('PM001', 'Studio Portraits', 'Professional studio portrait photography', 1, 1, 1, 5),
('PM002', 'Environmental Portraits', 'Portraits in natural and urban environments', 1, 1, 2, 5),
('ATL001', 'Modern Architecture', 'Contemporary architectural photography', 1, 1, 1, 6),
('ATL002', 'Historical Buildings', 'Heritage and historical architecture', 1, 1, 2, 6); 