-- =====================================================
-- Company Settings Table SQL (Law Firm Info)
-- =====================================================

CREATE TABLE IF NOT EXISTS `company_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) NOT NULL,
  `company_code` varchar(255) NOT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `address` text,
  `website` varchar(255) DEFAULT NULL,
  `tax_id` varchar(100) DEFAULT NULL,
  `currency_id` bigint unsigned DEFAULT NULL,
  `bank_details` json DEFAULT NULL,
  `logo_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_settings_company_code_unique` (`company_code`),
  KEY `company_settings_currency_id_foreign` (`currency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- System Settings Table SQL
-- =====================================================

CREATE TABLE IF NOT EXISTS `system_settings` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `default_currency_id` bigint unsigned DEFAULT NULL,
  `default_language` varchar(10) NOT NULL DEFAULT 'en',
  `timezone` varchar(50) NOT NULL DEFAULT 'UTC',
  `date_format` varchar(20) NOT NULL DEFAULT 'Y-m-d',
  `number_format` varchar(20) NOT NULL DEFAULT 'en',
  `email_notifications` tinyint(1) DEFAULT 1,
  `auto_backup_frequency` varchar(20) NOT NULL DEFAULT 'weekly',
  `case_number_prefix` varchar(10) NOT NULL DEFAULT 'CASE',
  `invoice_number_prefix` varchar(10) NOT NULL DEFAULT 'INV',
  `quotation_number_prefix` varchar(10) NOT NULL DEFAULT 'QUO',
  `receipt_number_prefix` varchar(10) NOT NULL DEFAULT 'REC',
  `default_case_status` varchar(50) NOT NULL DEFAULT 'Open',
  `default_payment_terms` int NOT NULL DEFAULT 30,
  `tax_calculation_method` varchar(20) NOT NULL DEFAULT 'exclusive',
  `file_upload_size_limit` int NOT NULL DEFAULT 10,
  `session_timeout` int NOT NULL DEFAULT 60,
  `maintenance_mode` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `system_settings_default_currency_id_foreign` (`default_currency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Optional: System Settings History Table
-- =====================================================

CREATE TABLE IF NOT EXISTS `system_settings_history` (
  `history_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `setting_id` bigint unsigned NOT NULL,
  `setting_key` varchar(191) DEFAULT NULL,
  `old_value` text,
  `new_value` text,
  `changed_by` bigint unsigned DEFAULT NULL,
  `changed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`history_id`),
  KEY `system_settings_history_setting_id_foreign` (`setting_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- Optional: System Settings Categories Table
-- =====================================================

CREATE TABLE IF NOT EXISTS `system_settings_categories` (
  `category_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `category_name` varchar(100) NOT NULL,
  `category_description` text,
  `sort_order` int DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
