sal.sql

CREATE TABLE `appointment_slots` (
  `id` int NOT NULL AUTO_INCREMENT,
  `appointment_slot_id` varchar(45) NOT NULL COMMENT 'Unique appointment slot id',
  `order_id` varchar(45) NOT NULL COMMENT 'Appointment order from which slots are booked',
  `client_id` varchar(45) NOT NULL COMMENT 'Client id who booked the appointment',
  `counsellor_id` varchar(45) NOT NULL COMMENT 'Counsellor/listener id to whom appointment has been made',
  `slots_bought` int NOT NULL COMMENT 'Total slots bought by client with counsellor/listener',
  `slots_remaining` int NOT NULL COMMENT 'Slots remaining for the client with counsellor/listener',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT 'Appointment slot status\n1 - active',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `appointment_slot_id_UNIQUE` (`appointment_slot_id`),
  UNIQUE KEY `order_id_UNIQUE` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Client slots booked and remaining with counsellor/listener';

CREATE TABLE `appointments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `appointment_id` varchar(45) NOT NULL COMMENT 'Unique appointment id',
  `order_id` varchar(45) NOT NULL COMMENT 'Appointment order from which slots are booked',
  `client_id` varchar(45) NOT NULL COMMENT 'Client id who booked the appointment',
  `counsellor_id` varchar(45) NOT NULL COMMENT 'Counsellor/listener id to whom appointment has been made',
  `date` date NOT NULL COMMENT 'Date at which appointment is book - format (2021-03-16)',
  `time` int NOT NULL COMMENT 'Hourly slot at which appointment is booked in the specified date - ranges from 0 - 23 IST hourly slots',
  `cancellation_reason` text COMMENT 'Reason for cancellation, if any',
  `rating` double DEFAULT NULL COMMENT 'Rating after appointment',
  `comment` text COMMENT 'Any comments when rating',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT 'Appointment status\n1 - to be started\n2 - started\n3 - completed',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `appointment_id_UNIQUE` (`appointment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Counsellor/listener appointments with clients';

CREATE TABLE `clients` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` varchar(45) NOT NULL COMMENT 'Unique client id for each signup client',
  `first_name` varchar(45) NOT NULL COMMENT 'First name of client',
  `last_name` varchar(45) DEFAULT NULL COMMENT 'Last name of client',
  `phone` varchar(45) NOT NULL COMMENT 'Unique phone number - client can be discovered whether he/she logs in/signs up with phone number',
  `photo` varchar(100) DEFAULT NULL COMMENT 'Upload client photo to S3 and place just photo name here (client/dsakjjqwennas.jpg)',
  `email` varchar(45) NOT NULL COMMENT 'Client email',
  `device_id` varchar(100) DEFAULT NULL,
  `location` varchar(45) DEFAULT NULL COMMENT 'Client location - to be sent by app',
  `status` tinyint DEFAULT NULL COMMENT 'Client status\n1 - active\n2 - deleted by client\n3 - blocked by admin',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `customer_id_UNIQUE` (`client_id`),
  UNIQUE KEY `phone_UNIQUE` (`phone`),
  UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Client list - normal users who want to explore, book appointment/event etc';

CREATE TABLE `counsellor_languages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `counsellor_id` varchar(45) NOT NULL COMMENT 'Counsellor/listener id',
  `language_id` tinyint NOT NULL COMMENT 'Language id - get it from languages table',
  PRIMARY KEY (`id`),
  UNIQUE KEY `counsellor_language_unqiue` (`language_id`,`counsellor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Languages that counsellor/listener can speak';

CREATE TABLE `counsellor_topics` (
  `id` int NOT NULL AUTO_INCREMENT,
  `counsellor_id` varchar(45) NOT NULL COMMENT 'Counsellor/listener id',
  `topic_id` tinyint NOT NULL COMMENT 'Topic id - get it from topics table',
  PRIMARY KEY (`id`),
  UNIQUE KEY `counsellor_topic_unique` (`topic_id`,`counsellor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Mental topics that counsellor/listener can address ';

CREATE TABLE `counsellors` (
  `id` int NOT NULL AUTO_INCREMENT,
  `counsellor_id` varchar(45) NOT NULL COMMENT 'Unique counsellor id',
  `first_name` varchar(45) NOT NULL COMMENT 'Counsellor first name',
  `last_name` varchar(45) DEFAULT NULL COMMENT 'Counsellor last name',
  `phone` varchar(45) NOT NULL COMMENT 'Unique phone number - counsellor can be discovered whether he/she logs in/signs up with phone number',
  `email` varchar(45) DEFAULT NULL COMMENT 'Counsellor email',
  `device_id` varchar(100) DEFAULT NULL,
  `gender` varchar(45) DEFAULT NULL COMMENT 'male,female,other',
  `total_rating` int NOT NULL DEFAULT '0' COMMENT 'Total number rating for counsellor appointments',
  `average_rating` float NOT NULL DEFAULT '0' COMMENT 'Average rating for counsellor appointments',
  `photo` varchar(100) DEFAULT NULL COMMENT 'Upload counsellor photo to S3 and place just photo name here (counsellor/dsakjjqwennas.jpg)',
  `price` double NOT NULL DEFAULT '0' COMMENT 'Price of 1 appointment - set by counsellor\n0 if not set',
  `price_3` double NOT NULL DEFAULT '0' COMMENT 'Price of 3 appointments - set by counsellor\n0 if not set',
  `price_5` double NOT NULL DEFAULT '0' COMMENT 'Price of 5 appointments - set by counsellor\n0 if not set',
  `education` text COMMENT 'Counsellor education - highest qualification like Counselling Psychologist, Psychiatrist, Psychotherapist, Others',
  `experience` text COMMENT 'Counsellor experience in the field in years',
  `about` text COMMENT 'About counsellor',
  `resume` varchar(100) DEFAULT NULL COMMENT 'Resume uploaded by counsellor - Upload file to S3 and place just file name here (counsellor/dsakjjqwennas.pdf)',
  `certificate` varchar(100) DEFAULT NULL COMMENT 'Certificate uploaded by counsellor - Upload file to S3 and place just file name here (counsellor/dsakjjqwennas.pdf)',
  `aadhar` varchar(100) DEFAULT NULL COMMENT 'Aadhar uploaded by counsellor - Upload file to S3 and place just file name here (counsellor/dsakjjqwennas.pdf)',
  `linkedin` varchar(100) DEFAULT NULL,
  `payout_percentage` int NOT NULL DEFAULT '10' COMMENT 'Percentage paid to counsellor for an appointment - will be set by admins after an agreement with counsellor',
  `payee_name` varchar(100) DEFAULT NULL COMMENT 'Counsellor bank details',
  `account_no` varchar(100) DEFAULT NULL COMMENT 'Counsellor bank details',
  `ifsc` varchar(100) DEFAULT NULL COMMENT 'Counsellor bank details',
  `branch_name` varchar(100) DEFAULT NULL COMMENT 'Counsellor bank details',
  `bank_name` varchar(100) DEFAULT NULL COMMENT 'Counsellor bank details',
  `pan` varchar(100) DEFAULT NULL COMMENT 'Counsellor PAN',
  `status` tinyint DEFAULT '0' COMMENT 'Counsellor status\n0 - not approved by admin\n1 - active\n2 - inactive for some reason\n3 - blocked by admin',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `counsellor_id_UNIQUE` (`counsellor_id`),
  UNIQUE KEY `phone_UNIQUE` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Counsellor list - professional therapists, can also create events';

CREATE TABLE `coupons` (
  `id` int NOT NULL AUTO_INCREMENT,
  `coupon_code` varchar(45) NOT NULL COMMENT 'Coupon code - not unique',
  `client_id` varchar(45) DEFAULT NULL COMMENT 'Client specific coupon code if not null',
  `counsellor_id` varchar(45) DEFAULT NULL COMMENT 'Counsellor specific coupon code if not null',
  `discount` double NOT NULL DEFAULT '0' COMMENT 'Discount based on discount type\nFlat - discounted amount\nPercentage - discount percentage',
  `minimum_order_value` double DEFAULT NULL COMMENT 'Minimum order value (before GST) from which coupon will be applied',
  `maximum_discount_value` double DEFAULT NULL COMMENT 'Maximum discount amount that can be given - in case of percentage discount type',
  `valid_for_order` tinyint DEFAULT NULL COMMENT 'Client order number for which coupon can be applicable\nIf 5 - coupon is applicable only if client has booked 4 appointments/events and applicable on only 5th order\nIf null, applicable to all orders',
  `type` tinyint NOT NULL DEFAULT '1' COMMENT 'Discount type\n1 - flat\n2 - percent',
  `order_type` tinyint DEFAULT NULL COMMENT 'Coupon applicable to\n1 - appointment\n2 - event\nNull - both',
  `start_by` datetime NOT NULL COMMENT 'Coupon starts from',
  `end_by` datetime NOT NULL COMMENT 'Coupon ends by',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT 'Coupon status\n0 - inactive\n1 - active',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Coupons list - can be applied for both appointments and events';

CREATE TABLE `invoices` (
  `id` int NOT NULL AUTO_INCREMENT,
  `invoice_id` varchar(45) NOT NULL COMMENT 'Unique invoice id',
  `user_id` varchar(45) NOT NULL COMMENT 'Client id\nWill be null if counsellor event creation order',
  `order_id` varchar(45) NOT NULL COMMENT 'Order id - based on user and order type\nUser = client, order = appointment  - order_client_appointments\nUser = client, order = event - order_client_events\nUser = counsellor, order = event - order_counsellor_events',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT 'Invoice status\n1 - in progress - order still not completed or rejected\n2 - refund initiated - order rejected, but still not refunded\n3 - refunded from payment provider\n4 - order completed',
  `user_type` tinyint DEFAULT NULL COMMENT 'Counsellor id type\n1 - counsellor\n2 - listener\n3 - client',
  `order_type` tinyint NOT NULL DEFAULT '1' COMMENT 'Order type\n1 - appointment\n2 - event',
  `actual_amount` double DEFAULT NULL COMMENT 'Actual amount paid',
  `tax` double DEFAULT NULL COMMENT 'Tax paid',
  `discount` double DEFAULT NULL COMMENT 'Discounted amount',
  `coupon_id` int DEFAULT NULL COMMENT 'Applied coupon id (incremental id in coupons table)',
  `coupon_code` varchar(45) DEFAULT NULL COMMENT 'Coupon code used to get discount',
  `paid_amount` double DEFAULT NULL COMMENT 'Total paid amount after tax, discount',
  `payment_method` varchar(45) DEFAULT NULL COMMENT 'Payment provider like Razorpay,payu,paytm',
  `payment_id` varchar(100) DEFAULT NULL COMMENT 'Payment provider payment id\nUsed for refunds',
  `payout_percentage` double DEFAULT NULL COMMENT 'Percentage payout to counsellor for order/event booking\nSince this can be changed in the counsellor table in the future, setting here for every invoice to know the payout percentage when invoice created',
  `refunded_amount` double DEFAULT NULL COMMENT 'Amount refunded on the invoice - since amount can be partially refunded, we need to store this',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_id_UNIQUE` (`invoice_id`),
  UNIQUE KEY `order_id_UNIQUE` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Invoice list - for all client appointment/event orders, counsellor event creation order';

CREATE TABLE `languages` (
  `id` int NOT NULL AUTO_INCREMENT,
  `language` varchar(45) NOT NULL COMMENT 'Name of language like English, hindi',
  PRIMARY KEY (`id`),
  UNIQUE KEY `language_UNIQUE` (`language`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Languages list';

CREATE TABLE `listeners` (
  `id` int NOT NULL AUTO_INCREMENT,
  `listener_id` varchar(45) NOT NULL COMMENT 'Unique listener id',
  `first_name` varchar(45) NOT NULL COMMENT 'Listener first name',
  `last_name` varchar(45) DEFAULT NULL COMMENT 'Listener last name',
  `gender` varchar(45) DEFAULT NULL COMMENT 'male,female,other',
  `phone` varchar(45) NOT NULL COMMENT 'Unique phone number - listener can be discovered whether he/she logs in/signs up with phone number',
  `email` varchar(45) DEFAULT NULL COMMENT 'Listener email',
  `device_id` varchar(100) DEFAULT NULL,
  `total_rating` int NOT NULL DEFAULT '0' COMMENT 'Total number rating for listener appointments',
  `average_rating` float NOT NULL DEFAULT '0' COMMENT 'Average rating for listener appointments',
  `photo` varchar(100) DEFAULT NULL COMMENT 'Upload listener photo to S3 and place just photo name here (listener/dsakjjqwennas.jpg)',
  `occupation` text COMMENT 'Listener education',
  `experience` text COMMENT 'Listener experience in the field',
  `about` text COMMENT 'About listener',
  `status` tinyint DEFAULT '0' COMMENT 'Listener status\n0 - not approved by admin\n1 - active\n2 - inactive for some reason\n3 - blocked by admin',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `listener_id_UNIQUE` (`listener_id`),
  UNIQUE KEY `phone_UNIQUE` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Listener list - professional listeners free of cost, cannot create events';

CREATE TABLE `notifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `notification_id` varchar(45) NOT NULL COMMENT 'Unique notification id',
  `user_id` varchar(45) NOT NULL COMMENT 'Can be client, counsellor or listener',
  `body` text NOT NULL COMMENT 'Notification body ',
  `notification_for` tinyint NOT NULL COMMENT 'Notification for\n1- counsellor\n2 - listener\n3 - Client',
  `type` tinyint NOT NULL COMMENT 'Notification type\n1 - Order successfully created\n\nStill not decided ',
  `status` tinyint NOT NULL DEFAULT '1' COMMENT 'Notification status\n1 - active\n2 - read\n3 - deleted',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `notification_id_UNIQUE` (`notification_id`)
) ENGINE=InnoDB DEFAULT AUTO_INCREMENT=1 CHARSET=latin1 COMMENT='Client and counsellor/listener notifications';

CREATE TABLE `order_client_appointments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` varchar(45) NOT NULL COMMENT 'Unique order id',
  `client_id` varchar(45) NOT NULL COMMENT 'Client id',
  `counsellor_id` varchar(45) NOT NULL COMMENT 'Both counsellor and listener ids',
  `date` date DEFAULT NULL COMMENT 'Don’t use this\nUse appointments for appointment date\nFormat - 2021-03-16\nUsed just for appointment order creation from it, if paid',
  `time` int DEFAULT NULL COMMENT 'Don’t use this\nUse appointments for appointment time\nHourly slot at which appointment is booked in the specified date - ranges from 0 - 23 hourly slots\nUsed just for appointment order creation from it, if paid',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT 'Order status\n0 - waiting\n1 - in progress ',
  `type` tinyint DEFAULT NULL COMMENT 'Booked for\n1- counsellor \n2 - listener',
  `slots_bought` int DEFAULT NULL COMMENT 'Number of appointment slots bought by client ',
  `actual_amount` double NOT NULL DEFAULT '0' COMMENT 'Actual amount - paid by client to create order',
  `tax` double NOT NULL DEFAULT '0' COMMENT 'Tax - paid by client to create order',
  `discount` double NOT NULL DEFAULT '0' COMMENT 'Discounted amount',
  `coupon_id` int DEFAULT NULL COMMENT 'Applied coupon id (incremental id in coupons table)',
  `coupon_code` varchar(45) DEFAULT NULL COMMENT 'Coupon code used to get discount',
  `paid_amount` double NOT NULL DEFAULT '0' COMMENT 'Total paid amount after tax, discount',
  `payout_percentage` double DEFAULT NULL COMMENT 'Percentage payout to counsellor for order/event booking\nSince this can be changed in the counsellor table in the future, setting here for every invoice to know the payout percentage when invoice created',
  `invoice_id` varchar(45) DEFAULT NULL COMMENT 'Invoice id, if paid',
  `cancellation_reason` varchar(45) DEFAULT NULL COMMENT 'Reason for cancellation, if any',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_id_UNIQUE` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Order list - for all client appointment booking';

CREATE TABLE `order_client_events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` varchar(45) NOT NULL COMMENT 'Unique order id',
  `client_id` varchar(45) NOT NULL COMMENT 'Client id',
  `event_order_id` varchar(45) NOT NULL COMMENT 'Event for which client has booked',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT 'Order status\n0 - waiting\n1 - in progress ',
  `actual_amount` double NOT NULL DEFAULT '0' COMMENT 'Actual amount - paid by client to create order',
  `tax` double NOT NULL DEFAULT '0' COMMENT 'Tax - paid by client to create order',
  `discount` double NOT NULL DEFAULT '0' COMMENT 'Discounted amount',
  `coupon_id` int DEFAULT NULL COMMENT 'Applied coupon id (incremental id in coupons table)',
  `coupon_code` varchar(45) DEFAULT NULL COMMENT 'Coupon code used to get discount',
  `paid_amount` double NOT NULL DEFAULT '0' COMMENT 'Total paid amount after tax, discount',
  `invoice_id` varchar(45) DEFAULT NULL COMMENT 'Invoice id, if paid',
  `cancellation_reason` varchar(45) DEFAULT NULL COMMENT 'Reason for cancellation, if any',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_id_UNIQUE` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Order list - for all client event booking';

CREATE TABLE `order_counsellor_events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` varchar(45) NOT NULL COMMENT 'Unique event id',
  `counsellor_id` varchar(45) NOT NULL COMMENT 'Counsellor id, who created event',
  `title` varchar(255) NOT NULL COMMENT 'Event title',
  `description` text NOT NULL COMMENT 'Event description',
  `topic_id` tinyint NOT NULL DEFAULT '1' COMMENT 'Topic which will be covered in the event - get it from topic table',
  `date` date NOT NULL COMMENT 'Event date - format (20201-03-15)',
  `time` int NOT NULL COMMENT 'Hourly slot at which event will start in the specified date - ranges from 0 - 23 IST hourly slots',
  `duration` int NOT NULL DEFAULT '30' COMMENT 'Duration of event in minutes, set by counsellor',
  `price` double NOT NULL DEFAULT '0' COMMENT 'Price to be paid by client to book event - will be set by counsellor',
  `actual_amount` double NOT NULL COMMENT 'Actual amount - paid by counsellor to create event',
  `tax` double NOT NULL COMMENT 'Tax - paid by counsellor to create event',
  `paid_amount` double NOT NULL COMMENT 'Paid amount - paid by counsellor to create event',
  `invoice_id` varchar(45) DEFAULT NULL COMMENT 'Invoice for the amount paid by counsellor to create event',
  `status` tinyint DEFAULT '1' COMMENT 'Event status\n0 - event waiting for payment to be done by counsellor\n1 - to be started\n2 - started\n3 - completed',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`,`paid_amount`,`tax`,`actual_amount`),
  UNIQUE KEY `event_id_UNIQUE` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Events order list - created by counsellors';

CREATE TABLE `payments` (
  `id` int NOT NULL AUTO_INCREMENT,
  `payment_id` varchar(45) NOT NULL,
  `client_id` varchar(45) NOT NULL,
  `heading` text NOT NULL,
  `description` text,
  `total` double DEFAULT NULL,
  `gst` double DEFAULT NULL,
  `tds` double DEFAULT NULL,
  `net_amount` varchar(255) DEFAULT NULL,
  `bank_ref_no` varchar(255) DEFAULT NULL,
  `bank_account_no` varchar(255) DEFAULT NULL,
  `bank_ifsc_code` varchar(255) DEFAULT NULL,
  `paid_date_time` datetime NOT NULL,
  `comment` text,
  `status` tinyint NOT NULL DEFAULT '1',
  `created_by` varchar(45) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `modified_by` varchar(45) DEFAULT NULL,
  `modified_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payment_id_UNIQUE` (`payment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Payments made to counsellor\nStill not decided about schema';

CREATE TABLE `phone_otp_verified` (
  `id` int NOT NULL AUTO_INCREMENT,
  `phone` varchar(45) NOT NULL COMMENT 'Phone number',
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone_UNIQUE` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='Verified phone numbers - using for security reasons';

CREATE TABLE `schedules` (
  `id` int NOT NULL AUTO_INCREMENT,
  `counsellor_id` varchar(45) NOT NULL COMMENT 'Both counsellor and listener',
  `weekday` tinyint NOT NULL DEFAULT '1' COMMENT '0 - sunday\n1 - monday\n2 - Tuesday\n3 - Wednesday\n4 - thursday\n5 - friday\n6 - Saturday\n',
  `0` tinyint NOT NULL DEFAULT '0' COMMENT '0 - unavailable\n1 - available \n2 - booked',
  `1` tinyint NOT NULL DEFAULT '0',
  `2` tinyint NOT NULL DEFAULT '0',
  `3` tinyint NOT NULL DEFAULT '0',
  `4` tinyint NOT NULL DEFAULT '0',
  `5` tinyint NOT NULL DEFAULT '0',
  `6` tinyint NOT NULL DEFAULT '0',
  `7` tinyint NOT NULL DEFAULT '0',
  `8` tinyint NOT NULL DEFAULT '0',
  `9` tinyint NOT NULL DEFAULT '0',
  `10` tinyint NOT NULL DEFAULT '0',
  `11` tinyint NOT NULL DEFAULT '0',
  `12` tinyint NOT NULL DEFAULT '0',
  `13` tinyint NOT NULL DEFAULT '0',
  `14` tinyint NOT NULL DEFAULT '0',
  `15` tinyint NOT NULL DEFAULT '0',
  `16` tinyint NOT NULL DEFAULT '0',
  `17` tinyint NOT NULL DEFAULT '0',
  `18` tinyint NOT NULL DEFAULT '0',
  `19` tinyint NOT NULL DEFAULT '0',
  `20` tinyint NOT NULL DEFAULT '0',
  `21` tinyint NOT NULL DEFAULT '0',
  `22` tinyint NOT NULL DEFAULT '0',
  `23` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `counsellor_id_weekday_unique` (`counsellor_id`,`weekday`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Counsellor/listener weekly hourly schedule';

CREATE TABLE `slots` (
  `id` int NOT NULL AUTO_INCREMENT,
  `counsellor_id` varchar(45) NOT NULL COMMENT 'Both counsellor and listener',
  `date` date NOT NULL COMMENT 'Format - 2021-03-15',
  `0` tinyint DEFAULT '0' COMMENT '0 - unavailable\n1 - available\n2 - booked',
  `1` tinyint DEFAULT '0',
  `2` tinyint DEFAULT '0',
  `3` tinyint DEFAULT '0',
  `4` tinyint DEFAULT '0',
  `5` tinyint DEFAULT '0',
  `6` tinyint DEFAULT '0',
  `7` tinyint DEFAULT '0',
  `8` tinyint DEFAULT '0',
  `9` tinyint DEFAULT '0',
  `10` tinyint DEFAULT '0',
  `11` tinyint DEFAULT '0',
  `12` tinyint DEFAULT '0',
  `13` tinyint DEFAULT '0',
  `14` tinyint DEFAULT '0',
  `15` tinyint DEFAULT '0',
  `16` tinyint DEFAULT '0',
  `17` tinyint DEFAULT '0',
  `18` tinyint DEFAULT '0',
  `19` tinyint DEFAULT '0',
  `20` tinyint DEFAULT '0',
  `21` tinyint DEFAULT '0',
  `22` tinyint DEFAULT '0',
  `23` tinyint DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `counsellor_id_date_unique` (`date`,`counsellor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Counsellor/listener hourly schedule by date';

CREATE TABLE `topics` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'Name of topic like anger/professional etc',
  `topic` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `topic_UNIQUE` (`topic`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Mental topic list';