-- ========================================
-- Infinity Medical Database Setup SQL
-- Import this file in phpMyAdmin
-- ========================================

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

-- ========================================
-- Database: infinity_medical
-- ========================================

-- Table structure for `services` (Detailed Contact Forms)
CREATE TABLE IF NOT EXISTS `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `part` varchar(255) NOT NULL,
  `modality` varchar(255) NOT NULL,
  `manufacturer` varchar(255) NOT NULL,
  `part_description` text DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for `ser` (Simple Contact Forms)
CREATE TABLE IF NOT EXISTS `ser` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `part_description` text NOT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for `rental` (Rental Service Forms)
CREATE TABLE IF NOT EXISTS `rental` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(255) NOT NULL,
  `last_name` varchar(255) NOT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `rental_service` varchar(255) NOT NULL,
  `part_description` text DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for `parts` (Product Parts Management)
CREATE TABLE IF NOT EXISTS `parts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `part_number` varchar(255) NOT NULL,
  `manufacturer` varchar(255) NOT NULL,
  `modality` varchar(255) NOT NULL,
  `product` varchar(255) NOT NULL,
  `modal` varchar(255) DEFAULT NULL,
  `image` varchar(500) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_modality` (`modality`),
  KEY `idx_manufacturer` (`manufacturer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data for parts table (Optional)
INSERT INTO `parts` (`part_number`, `manufacturer`, `modality`, `product`, `modal`, `image`) VALUES
('CT-001', 'GE Healthcare', 'CT', 'CT Scanner', 'Optima CT540', '/api/uploads/sample-ct.jpg'),
('MRI-002', 'Siemens', 'MRI', 'MRI Scanner', 'Magnetom Essenza', '/api/uploads/sample-mri.jpg'),
('XR-003', 'Philips', 'X-Ray', 'Digital X-Ray', 'DigitalDiagnost C90', '/api/uploads/sample-xray.jpg'),
('US-004', 'Canon Medical', 'Ultrasound', 'Ultrasound System', 'Aplio i800', '/api/uploads/sample-us.jpg'),
('CR-005', 'Fujifilm', 'CR', 'Computed Radiography', 'FCR Velocity', '/api/uploads/sample-cr.jpg');

-- Create indexes for better performance
CREATE INDEX idx_services_submitted ON services(submitted_at);
CREATE INDEX idx_ser_submitted ON ser(submitted_at);
CREATE INDEX idx_rental_submitted ON rental(submitted_at);
CREATE INDEX idx_parts_created ON parts(created_at);

-- Set AUTO_INCREMENT starting values
ALTER TABLE `services` AUTO_INCREMENT = 1;
ALTER TABLE `ser` AUTO_INCREMENT = 1;
ALTER TABLE `rental` AUTO_INCREMENT = 1;
ALTER TABLE `parts` AUTO_INCREMENT = 1;

COMMIT;

-- ========================================
-- Database Setup Complete!
-- ========================================
-- Tables Created:
-- 1. services - Detailed contact forms
-- 2. ser - Simple contact forms  
-- 3. rental - Rental service forms
-- 4. parts - Product parts with images
-- 
-- Next Steps:
-- 1. Import this SQL file in phpMyAdmin
-- 2. Update your env.php with correct database name
-- 3. Test your website at: your-domain.com/api/setup.php
-- ========================================
