-- DPM Database Schema Export
-- Generated on 2026-08-13 21:06:25

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS `project_ledger`;
CREATE TABLE `project_ledger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `project_key` varchar(50) DEFAULT NULL,
  `transaction_date` date NOT NULL,
  `transaction_type` enum('addition','omission','invoice','dpm invoice','budget','estimate') NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `explanation` varchar(255) NOT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `file_url` varchar(255) DEFAULT NULL,
  `client_signature_required` tinyint(1) DEFAULT 0,
  `client_signed` tinyint(1) DEFAULT 0,
  `admin_received_email` tinyint(1) DEFAULT 0,
  `supplier_id` int(11) DEFAULT NULL,
  `payment_received` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `projects`;
CREATE TABLE `projects` (
  `project_key` varchar(50) NOT NULL,
  `project_name` varchar(100) NOT NULL,
  `status` int(11) NOT NULL DEFAULT 0,
  `pin` varchar(4) NOT NULL DEFAULT '0000',
  `progress_update` text DEFAULT NULL,
  PRIMARY KEY (`project_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `site_portfolio`;
CREATE TABLE `site_portfolio` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `image_url` varchar(255) NOT NULL,
  `display_order` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `site_settings`;
CREATE TABLE `site_settings` (
  `setting_key` varchar(50) NOT NULL,
  `setting_value` text DEFAULT NULL,
  PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `supplier_allocations`;
CREATE TABLE `supplier_allocations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `supplier_id` int(11) NOT NULL,
  `project_key` varchar(50) NOT NULL,
  `allocated_budget` decimal(10,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_sup_proj` (`supplier_id`,`project_key`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP TABLE IF EXISTS `suppliers`;
CREATE TABLE `suppliers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `business_name` varchar(255) DEFAULT NULL,
  `full_name` varchar(255) NOT NULL,
  `profile_name` varchar(255) DEFAULT '',
  `profile_picture_name` varchar(255) DEFAULT '',
  `pin` varchar(4) NOT NULL DEFAULT '0000',
  `trade_type` varchar(100) NOT NULL,
  `profile_text` text DEFAULT NULL,
  `profile_image` varchar(255) DEFAULT NULL,
  `work_image_1` varchar(255) DEFAULT NULL,
  `work_image_2` varchar(255) DEFAULT NULL,
  `work_image_3` varchar(255) DEFAULT NULL,
  `work_image_4` varchar(255) DEFAULT NULL,
  `work_image_5` varchar(255) DEFAULT NULL,
  `telephone` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `contractor_name` varchar(255) DEFAULT NULL,
  `supplier_corporate_name` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `contract_text` text DEFAULT NULL,
  `contract_signed` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;