-- Database Restructure: Separate Authentication and Profile Data
-- Date: January 13, 2026

-- ============================================
-- STEP 1: Create new user_details table
-- ============================================

CREATE TABLE IF NOT EXISTS `user_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `contactno` varchar(15) DEFAULT NULL,
  `shippingAddress` longtext,
  `shippingState` varchar(255) DEFAULT NULL,
  `shippingCity` varchar(255) DEFAULT NULL,
  `shippingPincode` int(11) DEFAULT NULL,
  `billingAddress` longtext,
  `billingState` varchar(255) DEFAULT NULL,
  `billingCity` varchar(255) DEFAULT NULL,
  `billingPincode` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `user_details_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- STEP 2: Add username column to users table
-- ============================================

ALTER TABLE `users` 
ADD COLUMN `username` varchar(100) DEFAULT NULL AFTER `id`,
ADD UNIQUE KEY `username` (`username`);

-- ============================================
-- STEP 3: Migrate existing customer data
-- ============================================

-- Copy customer details to user_details table
INSERT INTO `user_details` (
    user_id, 
    name, 
    contactno, 
    shippingAddress, 
    shippingState, 
    shippingCity, 
    shippingPincode,
    billingAddress,
    billingState,
    billingCity,
    billingPincode
)
SELECT 
    id,
    name,
    contactno,
    shippingAddress,
    shippingState,
    shippingCity,
    shippingPincode,
    billingAddress,
    billingState,
    billingCity,
    billingPincode
FROM `users`
WHERE role = 'customer' OR role IS NULL OR role = '';

-- ============================================
-- STEP 4: Migrate admin accounts from admin table
-- ============================================

-- Insert admins from admin table (if exists)
INSERT INTO `users` (username, password, role)
SELECT 
    adminName,
    password,
    'admin'
FROM `admin`
WHERE adminName NOT IN (SELECT username FROM users WHERE username IS NOT NULL)
ON DUPLICATE KEY UPDATE username = adminName;

-- ============================================
-- STEP 5: Clean up users table
-- ============================================

-- Remove profile columns from users table (keep authentication only)
-- Note: Uncomment these AFTER verifying migration was successful
-- ALTER TABLE `users` DROP COLUMN `name`;
-- ALTER TABLE `users` DROP COLUMN `contactno`;
-- ALTER TABLE `users` DROP COLUMN `shippingAddress`;
-- ALTER TABLE `users` DROP COLUMN `shippingState`;
-- ALTER TABLE `users` DROP COLUMN `shippingCity`;
-- ALTER TABLE `users` DROP COLUMN `shippingPincode`;
-- ALTER TABLE `users` DROP COLUMN `billingAddress`;
-- ALTER TABLE `users` DROP COLUMN `billingState`;
-- ALTER TABLE `users` DROP COLUMN `billingCity`;
-- ALTER TABLE `users` DROP COLUMN `billingPincode`;

-- ============================================
-- VERIFICATION QUERIES
-- ============================================

-- Check users table
SELECT id, username, email, role FROM users;

-- Check user_details table
SELECT user_id, name, contactno FROM user_details;

-- Check customer count matches
SELECT 
    (SELECT COUNT(*) FROM users WHERE role = 'customer') as users_customers,
    (SELECT COUNT(*) FROM user_details) as details_records;

-- ============================================
-- NOTES
-- ============================================

/*
Structure after migration:

users (authentication):
- id
- username (for admins)
- email (for customers)
- password
- role
- regDate
- updationDate

user_details (customer profiles):
- id
- user_id (FK to users.id)
- name
- contactno
- shipping/billing addresses
- created_at
- updated_at
*/
