-- MySQL 8+ schema for PIARC Toolkit
-- Adjust database name as needed.
CREATE TABLE `app_config` (
  `key` varchar(64) NOT NULL,
  `value` longtext NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `countries` (
  `code` varchar(8) NOT NULL,
  `name` varchar(128) NOT NULL,
  `currency` varchar(8) DEFAULT NULL,
  `currency_symbol` varchar(16) DEFAULT NULL,
  `currency_rate_to_USD` decimal(16,6) DEFAULT NULL,
  `unit_speed` varchar(16) DEFAULT NULL,
  `emission_factor_petrol` decimal(12,4) DEFAULT NULL,
  `emission_factor_diesel` decimal(12,4) DEFAULT NULL,
  `value_of_time_passenger` decimal(12,2) DEFAULT NULL,
  `value_of_time_heavy` decimal(12,2) DEFAULT NULL,
  `occupancy_default` decimal(10,3) DEFAULT NULL,
  `fuel_efficiency_passenger` decimal(10,3) DEFAULT NULL,
  `fuel_efficiency_heavy` decimal(10,3) DEFAULT NULL,
  `fuel_price_per_unit` decimal(10,3) DEFAULT NULL,
  `fuel_price_unit` varchar(16) DEFAULT NULL,
  `carbon_price` decimal(12,2) DEFAULT NULL,
  `assumption_notes` longtext DEFAULT NULL,
  PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(190) NOT NULL,
  `name` varchar(190) NOT NULL DEFAULT '',
  `password_hash` varchar(190) NOT NULL,
  `role` enum('admin','editor','viewer') NOT NULL DEFAULT 'viewer',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `policies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` text DEFAULT NULL,
  `title` text DEFAULT NULL,
  `source_url` text DEFAULT NULL,
  `source__url` text DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `type` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=450 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `feedback` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(190) NOT NULL DEFAULT '',
  `email` varchar(190) NOT NULL DEFAULT '',
  `page` varchar(255) NOT NULL DEFAULT '',
  `subject` varchar(255) NOT NULL DEFAULT '',
  `message` text NOT NULL,
  `rating` tinyint(4) DEFAULT NULL,
  `status` enum('new','in_review','resolved','archived') NOT NULL DEFAULT 'new',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;