-- phpMyAdmin SQL Dump
-- version 5.2.3
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: May 04, 2026 at 03:16 PM
-- Server version: 8.0.45
-- PHP Version: 8.3.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `jobcardpro_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `email_queue`
--

CREATE TABLE `email_queue` (
  `id` int NOT NULL,
  `recipient_email` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` longtext NOT NULL,
  `attachments` text,
  `status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `error_message` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sent_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `jobcards`
--

CREATE TABLE `jobcards` (
  `id` int NOT NULL,
  `jobcard_number` varchar(20) NOT NULL,
  `client_id` int NOT NULL,
  `technician_id` int DEFAULT NULL,
  `partner_id` int DEFAULT NULL,
  `status` enum('Created','Assigned','Started','Completed','Signed Off') NOT NULL DEFAULT 'Created',
  `instructions` text NOT NULL,
  `work_done` text,
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `tech_signature` longtext,
  `client_signature` longtext,
  `client_sign_name` varchar(255) DEFAULT NULL,
  `tech_sign_name` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `jobcard_materials`
--

CREATE TABLE `jobcard_materials` (
  `id` int NOT NULL,
  `jobcard_id` int NOT NULL,
  `material_name` varchar(255) NOT NULL,
  `quantity` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `partners`
--

CREATE TABLE `partners` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `contact_number` varchar(50) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `address_street` varchar(255) DEFAULT NULL,
  `address_suburb` varchar(255) DEFAULT NULL,
  `address_city` varchar(100) DEFAULT NULL,
  `address_state` varchar(100) DEFAULT NULL,
  `address_zip` varchar(20) DEFAULT NULL,
  `address_country` varchar(100) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `jobcard_prefix` varchar(20) DEFAULT 'PRT-',
  `jobcard_sequence` int NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `password_resets`
--

CREATE TABLE `password_resets` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int NOT NULL,
  `setting_key` varchar(50) NOT NULL,
  `setting_value` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `setting_key`, `setting_value`) VALUES
(1, 'app_name', 'Jobcard Manager Pro'),
(2, 'seo_description', 'Professional Jobcard Manager'),
(3, 'seo_keywords', 'Jobcard Manager Pro, christodev, developer, development'),
(4, 'logo_light', 'logo_light.png'),
(5, 'logo_dark', 'logo_dark.png'),
(6, 'favicon', 'favicon.png'),
(7, 'theme_color', '#1c70b4'),
(8, 'allow_registration', '1'),
(9, 'privacy_policy', '<p>Privacy Policy goes here.</p>'),
(10, 'terms_of_service', '<p>Terms of service goes here.</p>'),
(11, 'contact_email', 'admin@yourdomain.com'),
(24, 'smtp_host', ''),
(25, 'smtp_port', '465'),
(26, 'smtp_user', ''),
(27, 'smtp_pass', ''),
(28, 'smtp_from_name', ''),
(29, 'smtp_from_email', ''),
(30, 'smtp_bcc_enable', '0'),
(31, 'smtp_bcc_email', ''),
(49, 'maintenance_mode', '0'),
(195, 'db_version', '0'),
(198, 'allow_developer_support', '0'),
(465, 'contact_address_street', ''),
(466, 'contact_address_city', ''),
(467, 'contact_address_state', ''),
(468, 'contact_address_zip', ''),
(469, 'contact_address_country', ''),
(493, 'contact_address_suburb', ''),
(518, 'smtp_encryption', 'ssl'),
(520, 'contact_landline', ''),
(521, 'contact_mobile', ''),
(576, 'cron_token', 'xK9pM2vL5nQ8jH4bC7zR3wT6yJ1fF0dD');

-- --------------------------------------------------------

--
-- Table structure for table `staff_roles`
--

CREATE TABLE `staff_roles` (
  `id` int NOT NULL,
  `role_name` varchar(100) NOT NULL,
  `description` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `support_tickets`
--

CREATE TABLE `support_tickets` (
  `id` int NOT NULL,
  `user_id` int NOT NULL,
  `ticket_number` varchar(20) NOT NULL,
  `ticket_type` varchar(100) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` varchar(50) NOT NULL DEFAULT 'Open',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `system_logs`
--

CREATE TABLE `system_logs` (
  `id` int NOT NULL,
  `user_id` int DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` varchar(20) NOT NULL DEFAULT 'client',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `email_verified` tinyint(1) NOT NULL DEFAULT '1',
  `verification_token` varchar(255) DEFAULT NULL,
  `remember_token` varchar(255) DEFAULT NULL,
  `failed_attempts` int NOT NULL DEFAULT '0',
  `locked_until` datetime DEFAULT NULL,
  `totp_secret` varchar(255) DEFAULT NULL,
  `totp_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `contact_number` varchar(50) DEFAULT NULL,
  `address_street` varchar(255) DEFAULT NULL,
  `address_suburb` varchar(255) DEFAULT NULL,
  `address_city` varchar(100) DEFAULT NULL,
  `address_state` varchar(100) DEFAULT NULL,
  `address_zip` varchar(20) DEFAULT NULL,
  `address_country` varchar(100) DEFAULT NULL,
  `staff_role_id` int DEFAULT NULL,
  `partner_id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- --------------------------------------------------------

--
-- Table structure for table `user_backup_codes`
--

CREATE TABLE `user_backup_codes` (
  `id` int NOT NULL,
  `user_id` int NOT NULL,
  `code_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `used` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `used_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

ALTER TABLE `email_queue` ADD PRIMARY KEY (`id`);
ALTER TABLE `jobcards` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `idx_jobcard_number` (`jobcard_number`), ADD KEY `client_id` (`client_id`), ADD KEY `technician_id` (`technician_id`), ADD KEY `partner_id` (`partner_id`);
ALTER TABLE `jobcard_materials` ADD PRIMARY KEY (`id`), ADD KEY `jobcard_id` (`jobcard_id`);
ALTER TABLE `partners` ADD PRIMARY KEY (`id`);
ALTER TABLE `password_resets` ADD KEY `email` (`email`);
ALTER TABLE `settings` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `setting_key` (`setting_key`);
ALTER TABLE `staff_roles` ADD PRIMARY KEY (`id`);
ALTER TABLE `support_tickets` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`);
ALTER TABLE `system_logs` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`);
ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD KEY `fk_users_staff_role` (`staff_role_id`), ADD KEY `fk_users_partner` (`partner_id`);
ALTER TABLE `user_backup_codes` ADD PRIMARY KEY (`id`), ADD KEY `user_id` (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

ALTER TABLE `email_queue` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `jobcards` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `jobcard_materials` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `partners` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `settings` MODIFY `id` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=953;
ALTER TABLE `staff_roles` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `support_tickets` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `system_logs` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `users` MODIFY `id` int NOT NULL AUTO_INCREMENT;
ALTER TABLE `user_backup_codes` MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

ALTER TABLE `jobcards`
  ADD CONSTRAINT `fk_jobcard_client` FOREIGN KEY (`client_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_jobcard_partner` FOREIGN KEY (`partner_id`) REFERENCES `partners` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_jobcard_tech` FOREIGN KEY (`technician_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

ALTER TABLE `jobcard_materials`
  ADD CONSTRAINT `fk_material_jobcard` FOREIGN KEY (`jobcard_id`) REFERENCES `jobcards` (`id`) ON DELETE CASCADE;

ALTER TABLE `users`
  ADD CONSTRAINT `fk_users_partner` FOREIGN KEY (`partner_id`) REFERENCES `partners` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_users_staff_role` FOREIGN KEY (`staff_role_id`) REFERENCES `staff_roles` (`id`) ON DELETE SET NULL;

ALTER TABLE `user_backup_codes`
  ADD CONSTRAINT `user_backup_codes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;