Um9DCXNR

1
2CREATE TABLE IF NOT EXISTS `customers` (
3  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
4  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
5  `gender` enum('Male','Female') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Male',
6  `type` enum('Lead','Insured') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Lead',
7  `phone_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
8  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
9  `address` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
10  `pincode` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
11  `status` enum('Active','Deleted') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
12  `created_by` int(10) unsigned NOT NULL,
13  `created_at` timestamp NULL DEFAULT NULL,
14  `updated_at` timestamp NULL DEFAULT NULL,
15  `state_id` int(10) unsigned NOT NULL DEFAULT '1',
16  `district_id` int(10) unsigned NOT NULL DEFAULT '1',
17  `policy_id` int(10) unsigned DEFAULT NULL,
18  PRIMARY KEY (`id`),
19  KEY `customers_created_by_foreign` (`created_by`),
20  KEY `customers_state_id_foreign` (`state_id`),
21  KEY `customers_district_id_foreign` (`district_id`),
22  KEY `customers_policy_id_foreign` (`policy_id`),
23  CONSTRAINT `customers_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
24  CONSTRAINT `customers_district_id_foreign` FOREIGN KEY (`district_id`) REFERENCES `districts` (`id`),
25  CONSTRAINT `customers_policy_id_foreign` FOREIGN KEY (`policy_id`) REFERENCES `policies` (`id`),
26  CONSTRAINT `customers_state_id_foreign` FOREIGN KEY (`state_id`) REFERENCES `states` (`id`)
27) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
28
29CREATE TABLE IF NOT EXISTS `policies` (
30  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
31  `insurer_id` int(10) unsigned NOT NULL,
32  `policy_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
33  `vehicle_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
34  `sum_insured` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
35  `policy_period_from` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
36  `policy_period_to` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
37  `premium_amt` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
38  `payment_date` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
39  `gst` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
40  `payment_method` enum('Cash','Cheque','Card') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Cash',
41  `status` enum('Active','Deleted') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
42  `created_by` int(10) unsigned NOT NULL,
43  `created_at` timestamp NULL DEFAULT NULL,
44  `updated_at` timestamp NULL DEFAULT NULL,
45  `policy_type_id` int(10) unsigned NOT NULL DEFAULT '1',
46  `cheque_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
47  `psop_id` int(10) unsigned NOT NULL DEFAULT '1',
48  PRIMARY KEY (`id`),
49  KEY `policy_created_by_foreign` (`created_by`),
50  KEY `policy_insurer_id_foreign` (`insurer_id`),
51  KEY `policies_policy_type_id_foreign` (`policy_type_id`),
52  KEY `policies_posp_id_foreign` (`psop_id`),
53  CONSTRAINT `policies_policy_type_id_foreign` FOREIGN KEY (`policy_type_id`) REFERENCES `policy_types` (`id`),
54  CONSTRAINT `policies_posp_id_foreign` FOREIGN KEY (`psop_id`) REFERENCES `users` (`id`),
55  CONSTRAINT `policy_created_by_foreign` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
56  CONSTRAINT `policy_insurer_id_foreign` FOREIGN KEY (`insurer_id`) REFERENCES `insurers` (`id`)
57) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;