create database Lawnlink;
use Lawnlink;


CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `mobile` varchar(15) NOT NULL,
  `passcode` varchar(255) NOT NULL,
  `create_on` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_login` timestamp NULL DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT '1',
  `is_deleted` tinyint(1) DEFAULT '0',
  `custom_user_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `mobile` (`mobile`),
  UNIQUE KEY `custom_user_id` (`custom_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=53 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



DELIMITER $$

CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    DECLARE new_id INT;
    DECLARE year_prefix VARCHAR(6);

    SET year_prefix = CONCAT('LL', YEAR(CURDATE()));

    SELECT IFNULL(MAX(CAST(SUBSTR(custom_user_id, 7) AS UNSIGNED)), 0) + 1 INTO new_id
    FROM users
    WHERE custom_user_id LIKE CONCAT(year_prefix, '%');

    SET NEW.custom_user_id = CONCAT(year_prefix, LPAD(new_id, 4, '0'));
END $$

DELIMITER ;


CREATE TABLE `userdetails` (
  `id` int NOT NULL AUTO_INCREMENT,
  `custom_user_id` varchar(30) DEFAULT NULL,
  `firstname` varchar(255) DEFAULT NULL,
  `lastname` varchar(255) DEFAULT NULL,
  `profilepicurl` varchar(2048) DEFAULT NULL,
  `address` text,
  `state` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `zipcode` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `custom_user_id` (`custom_user_id`),
  CONSTRAINT `userdetails_ibfk_1` FOREIGN KEY (`custom_user_id`) REFERENCES `users` (`custom_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertUserAndDetails`(
    IN p_username VARCHAR(255),
    IN p_email VARCHAR(255),
    IN p_phone VARCHAR(20),
    IN p_password VARCHAR(200),
    OUT p_custom_user_id  varchar(20) 
)
BEGIN
    DECLARE new_id varchar(20);
    
	INSERT INTO `users`
	(
	`user_name`,
	`email`,
	`mobile`,
	`passcode`,
	`create_on`,
	`is_active`,
	`is_deleted`)
	VALUES
	(
	p_username,
	p_email,
	p_phone,
	p_password,
	UTC_TIMESTAMP(),
	1,
	0);

    SET new_id = (select custom_user_id from users where user_id = LAST_INSERT_ID() Limit 1);

    INSERT INTO userdetails (custom_user_id) 
    VALUES (new_id);

    SET p_custom_user_id = new_id;
END$$
DELIMITER ;