-- ============================================================================
-- COMPANY TEMPLATE — Real schema (19 tables) with widened columns
-- Executed by CompanyCreator.php for every new company created via SaaS
-- ============================================================================

CREATE TABLE `backup_log` (
  `id` int(11) NOT NULL,
  `action_type` enum('Backup','Restore') NOT NULL,
  `filename` varchar(255) NOT NULL,
  `file_size` varchar(50) DEFAULT NULL,
  `tables_count` int(11) DEFAULT 0,
  `records_count` int(11) DEFAULT 0,
  `status` enum('Success','Failed') NOT NULL DEFAULT 'Success',
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `clients` (
  `id` int(11) NOT NULL,
  `client_customid` varchar(11) DEFAULT NULL,
  `client_name` varchar(150) DEFAULT NULL,
  `client_address` varchar(150) DEFAULT NULL,
  `client_city` varchar(25) DEFAULT 'Bakersfield',
  `client_state` varchar(3) DEFAULT 'Ca.',
  `client_zip` varchar(5) DEFAULT NULL,
  `client_phone` varchar(10) DEFAULT NULL,
  `client_email` varchar(150) DEFAULT NULL,
  `client_last_invoice` varchar(8) DEFAULT '0',
  `client_last_date` date DEFAULT NULL,
  `client_last_total` decimal(10,2) DEFAULT 0.00,
  `client_contact` varchar(150) DEFAULT NULL,
  `client_status` varchar(8) DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `company` (
  `cname` varchar(150) NOT NULL,
  `caddress` varchar(150) NOT NULL,
  `ccity` varchar(50) NOT NULL,
  `cstate` varchar(30) NOT NULL DEFAULT 'Ca.',
  `czip` varchar(15) NOT NULL,
  `cphone` varchar(25) NOT NULL,
  `cemail` varchar(150) NOT NULL,
  `company_logo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cfg_color_scheme` varchar(30) NOT NULL DEFAULT 'verde-profesional' COMMENT 'Esquema de color del sistema: verde-profesional, azul-corporativo, oscuro-elegante, purpura-moderno',
  `cfg_font_size` varchar(20) NOT NULL DEFAULT 'mediana' COMMENT 'Tamano de fuente del sistema: pequena, mediana, grande, extra-grande'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `employees` (
  `employee_id` int(10) NOT NULL,
  `employee_customid` varchar(10) NOT NULL,
  `employee_name` varchar(150) NOT NULL,
  `employee_address` varchar(150) NOT NULL,
  `employee_city` varchar(25) NOT NULL DEFAULT 'Bakersfield',
  `employee_state` varchar(3) NOT NULL DEFAULT 'Ca.',
  `employee_zip` varchar(5) NOT NULL,
  `employee_phone` varchar(10) NOT NULL,
  `employee_email` varchar(150) NOT NULL,
  `employee_ss` varchar(11) NOT NULL,
  `employee_hiredate` date NOT NULL,
  `employee_role` varchar(100) NOT NULL,
  `employee_status` enum('Active','Inactive') NOT NULL DEFAULT 'Active',
  `employee_hourrate` decimal(10,2) NOT NULL DEFAULT 0.00,
  `employee_dayrate` decimal(10,2) NOT NULL DEFAULT 0.00,
  `employee_stoppeddate` date DEFAULT NULL,
  `employee_reason` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `employee_pays` (
  `pays_employeecustomid` varchar(10) NOT NULL,
  `pays_paiddate` date NOT NULL,
  `pays_from` date DEFAULT NULL,
  `pays_to` date DEFAULT NULL,
  `pays_paidworkedhours` decimal(10,2) NOT NULL DEFAULT 0.00,
  `pays_paidworkeddays` decimal(10,2) NOT NULL DEFAULT 0.00,
  `pays_paiddiscountamount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `pays_paiddiscountnote` text NOT NULL,
  `pays_paidmethod` varchar(11) NOT NULL,
  `pays_paidmethodreference` varchar(30) NOT NULL,
  `pays_paidsubtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `pays_paidtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `pays_paidstatus` enum('Active','Cancelled') NOT NULL DEFAULT 'Active',
  `pays_paidreason` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `estimates` (
  `estimate_id` int(10) NOT NULL,
  `estimate_customid` varchar(10) NOT NULL,
  `estimate_client_custid` varchar(10) NOT NULL,
  `estimate_date` date NOT NULL,
  `estimate_applytax` enum('Yes','No') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'No',
  `estimate_taxpercent` decimal(10,2) NOT NULL DEFAULT 0.00,
  `estimate_service_address` varchar(150) NOT NULL,
  `estimate_service-city` varchar(20) NOT NULL DEFAULT 'Bakersfield',
  `estimate_service_state` varchar(3) NOT NULL DEFAULT 'Ca.',
  `estimate_service_zip` varchar(5) NOT NULL,
  `estimate_service_phone` varchar(10) NOT NULL,
  `estimate_job` varchar(150) NOT NULL,
  `estimate_note` text NOT NULL,
  `estimate_deposit` decimal(10,2) NOT NULL DEFAULT 0.00,
  `estimate_subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `estimate_tax` decimal(10,2) NOT NULL DEFAULT 0.00,
  `estimate_total` decimal(10,2) NOT NULL DEFAULT 0.00,
  `estimate_status` varchar(10) NOT NULL DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `estimates_movs` (
  `estimate_movs_id` int(10) NOT NULL,
  `estimate_movs_customid` varchar(10) NOT NULL,
  `estimate_movs_qty` decimal(10,2) NOT NULL DEFAULT 0.00,
  `estimate_movs_type` varchar(6) NOT NULL DEFAULT 'List',
  `estimate_movs_description` varchar(150) NOT NULL,
  `estimate_movs_cost` decimal(10,2) NOT NULL DEFAULT 0.00,
  `estimate_movs_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `estimate_movs_amount` decimal(10,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `expenses` (
  `expenses_id` int(10) NOT NULL,
  `expenses_date` date DEFAULT current_timestamp(),
  `expenses_concept` varchar(150) DEFAULT NULL,
  `expenses_provider` varchar(150) DEFAULT NULL,
  `expenses_qty` decimal(10,2) NOT NULL DEFAULT 0.00,
  `expenses_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `expenses_subtotal` decimal(10,2) DEFAULT 0.00,
  `expenses_tax` decimal(10,2) NOT NULL DEFAULT 0.00,
  `expenses_total` decimal(10,2) NOT NULL DEFAULT 0.00,
  `expenses_method` varchar(50) DEFAULT NULL,
  `expenses_reference` varchar(150) DEFAULT NULL,
  `expenses_receipt` varchar(255) DEFAULT NULL,
  `expenses_note` varchar(500) DEFAULT NULL,
  `expenses_status` varchar(10) NOT NULL DEFAULT 'Active',
  `expenses_reason` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `expenses_concepts` (
  `expenses_concept_id` int(10) NOT NULL,
  `expenses_concept_customid` varchar(10) DEFAULT NULL,
  `expenses_concept_description` varchar(30) DEFAULT NULL,
  `expenses_concept_status` varchar(8) DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `invoices` (
  `invo_id` int(10) NOT NULL,
  `invo_customid` varchar(10) NOT NULL,
  `invo_client_customid` varchar(10) NOT NULL,
  `invo_date` date NOT NULL,
  `invo_applytax` enum('Yes','No') NOT NULL DEFAULT 'No',
  `invo_taxpercent` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invo_service_address` varchar(150) NOT NULL,
  `invo_service_city` varchar(20) NOT NULL,
  `invo_service_state` varchar(3) NOT NULL DEFAULT 'Ca.',
  `invo_service_zip` varchar(5) NOT NULL,
  `invo_service_phone` varchar(10) NOT NULL,
  `invo_po` varchar(8) NOT NULL,
  `invo_job` varchar(150) NOT NULL,
  `invo_note` text NOT NULL,
  `invo_damages` text DEFAULT NULL,
  `invo_damages_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invo_deposit` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invo_subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invo_tax` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invo_total` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invo_status` varchar(10) NOT NULL DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `invoices_movs` (
  `invoice_movs_invoicecustomid` varchar(10) NOT NULL,
  `invoice_movs_qty` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invoice_movs_type` varchar(6) NOT NULL DEFAULT 'List',
  `invoice_movs_description` varchar(150) NOT NULL,
  `invoice_movs_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invoice_movs_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invoice_movs_id` int(9) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `invoice_payments` (
  `invo_payment_id` int(10) NOT NULL,
  `invo_payment_invo_customid` varchar(10) NOT NULL,
  `invo_payment_date` date NOT NULL,
  `invo_payment_amount` decimal(10,2) NOT NULL DEFAULT 0.00,
  `invo_payment_method` varchar(11) NOT NULL,
  `invo_payment_reference` varchar(16) NOT NULL,
  `invo_payment_note` text NOT NULL,
  `invo_payment_status` varchar(20) NOT NULL DEFAULT 'Active',
  `invo_payment_reason` varchar(150) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `invoice_photos` (
  `photo_id` int(11) NOT NULL,
  `invo_id` int(10) NOT NULL,
  `photo_filename` varchar(255) NOT NULL,
  `photo_description` varchar(255) DEFAULT NULL,
  `uploaded_at` datetime NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

CREATE TABLE `products` (
  `id` int(10) NOT NULL,
  `product_customid` varchar(10) DEFAULT NULL,
  `product_name` varchar(150) DEFAULT NULL,
  `product_mycost` decimal(10,2) DEFAULT 0.00,
  `product_vendor1` varchar(100) NOT NULL,
  `product_vendor2` varchar(100) NOT NULL,
  `product_vendor3` varchar(100) NOT NULL,
  `product_vendor4` varchar(100) NOT NULL,
  `product_vendor5` varchar(100) NOT NULL,
  `product_vendor1price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `product_vendor2price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `product_vendor3price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `product_vendor4price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `product_vendor5price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `product_vendor1per` varchar(20) NOT NULL,
  `product_vendor2per` varchar(20) NOT NULL,
  `product_vendor3per` varchar(20) NOT NULL,
  `product_vendor4per` varchar(20) NOT NULL,
  `product_vendor5per` varchar(20) NOT NULL,
  `product_myprice` decimal(10,2) DEFAULT 0.00,
  `product_status` varchar(8) DEFAULT 'Active',
  `product_hasvendor` varchar(3) NOT NULL DEFAULT 'No'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `system_settings` (
  `setting_id` int(11) NOT NULL,
  `setting_key` varchar(50) NOT NULL,
  `setting_value` varchar(255) NOT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_by` varchar(100) NOT NULL DEFAULT 'Admin'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `vendors` (
  `vendor_id` int(10) NOT NULL,
  `vendor_customid` varchar(10) NOT NULL,
  `vendor_name` varchar(150) NOT NULL,
  `vendor_address` varchar(150) NOT NULL,
  `vendor_city` varchar(25) NOT NULL DEFAULT 'Bakersfield',
  `vendor_state` varchar(3) NOT NULL DEFAULT 'Ca.',
  `vendor_zip` varchar(5) NOT NULL,
  `vendor_phone1` varchar(10) NOT NULL,
  `vendor_phone2` varchar(10) NOT NULL,
  `vendor_email` varchar(150) NOT NULL,
  `vendor_status` varchar(8) NOT NULL DEFAULT 'Active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Primary Keys and Auto Increment
ALTER TABLE `backup_log`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `clients`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `employees`
  ADD PRIMARY KEY (`employee_id`);
ALTER TABLE `estimates`
  ADD PRIMARY KEY (`estimate_id`);
ALTER TABLE `estimates_movs`
  ADD PRIMARY KEY (`estimate_movs_id`);
ALTER TABLE `expenses`
  ADD PRIMARY KEY (`expenses_id`);
ALTER TABLE `expenses_concepts`
  ADD PRIMARY KEY (`expenses_concept_id`);
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`invo_id`);
ALTER TABLE `invoices_movs`
  ADD PRIMARY KEY (`invoice_movs_id`);
ALTER TABLE `invoice_payments`
  ADD PRIMARY KEY (`invo_payment_id`);
ALTER TABLE `invoice_photos`
  ADD PRIMARY KEY (`photo_id`),
  ADD KEY `idx_invo_id` (`invo_id`);
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `system_settings`
  ADD PRIMARY KEY (`setting_id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`);
ALTER TABLE `vendors`
  ADD PRIMARY KEY (`vendor_id`);
ALTER TABLE `backup_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
ALTER TABLE `clients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `employees`
  MODIFY `employee_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `estimates`
  MODIFY `estimate_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `estimates_movs`
  MODIFY `estimate_movs_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=23;
ALTER TABLE `expenses`
  MODIFY `expenses_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
ALTER TABLE `expenses_concepts`
  MODIFY `expenses_concept_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
ALTER TABLE `invoices`
  MODIFY `invo_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `invoices_movs`
  MODIFY `invoice_movs_id` int(9) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=42;
ALTER TABLE `invoice_payments`
  MODIFY `invo_payment_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `invoice_photos`
  MODIFY `photo_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
ALTER TABLE `products`
  MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `system_settings`
  MODIFY `setting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=54;
ALTER TABLE `vendors`
  MODIFY `vendor_id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

-- ============================================================================
-- DEFAULT DATA — so new companies work out-of-the-box
-- ============================================================================

-- Empty company row (filled later by user in Company Info module)
INSERT INTO `company` (`cname`, `caddress`, `ccity`, `cstate`, `czip`, `cphone`, `cemail`, `company_logo`, `cfg_color_scheme`, `cfg_font_size`)
VALUES ('New Company', '', 'Bakersfield', 'Ca.', '', '', '', 'company_logo.png', 'ocean', '14px');
