-- ============================================================================
-- Migration: Multi-Society User Architecture
-- 
-- This migration:
--   1. Creates the `user_society_mapping` table
--   2. Migrates existing user→society relationships into mapping rows
--   3. Adds `is_superadmin` flag to `users`
--   4. Updates unique constraints on `users` (email globally unique)
--   5. Drops old society-specific columns from `users`
--
-- ⚠️  IMPORTANT: Take a full database backup before running this migration!
-- ============================================================================

-- ----------------------------------------------------------------------------
-- STEP 1: Create the user_society_mapping table
-- ----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS user_society_mapping (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL,
    society_id          INT UNSIGNED NOT NULL,

    -- Role & Permissions (per-society)
    role                ENUM('admin','member','secretary','treasurer','chairperson','manager') NOT NULL DEFAULT 'member',
    is_admin            TINYINT(1) DEFAULT 0,
    is_member           TINYINT(1) DEFAULT 1,
    permissions         JSON,

    -- Society-specific member details
    flat_number         VARCHAR(20),
    wing                VARCHAR(10),
    vehicles            VARCHAR(255),
    family_members      INT,
    move_in_date        DATE,
    ownership           ENUM('owner','tenant') DEFAULT 'owner',

    -- Approval & Status
    is_approved         TINYINT(1) DEFAULT 0,
    is_active           TINYINT(1) DEFAULT 1,
    member_since        DATE,
    maintenance_status  ENUM('verified','unverified') DEFAULT 'unverified',

    -- Timestamps
    joined_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Constraints
    UNIQUE KEY uq_user_society (user_id, society_id),
    INDEX idx_usm_society_id (society_id),
    INDEX idx_usm_user_id (user_id),

    FOREIGN KEY fk_usm_user (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY fk_usm_society (society_id) REFERENCES society(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ----------------------------------------------------------------------------
-- STEP 2: Add `is_superadmin` column to `users`
-- ----------------------------------------------------------------------------

ALTER TABLE users ADD COLUMN is_superadmin TINYINT(1) DEFAULT 0 AFTER status;

-- Set is_superadmin = 1 for existing superadmin users
UPDATE users SET is_superadmin = 1 WHERE role = 'superadmin';


-- ----------------------------------------------------------------------------
-- STEP 3: Migrate existing data into user_society_mapping
-- 
-- For NON-superadmin users who have a valid society_id, copy their
-- society-specific data into the mapping table.
-- ----------------------------------------------------------------------------

INSERT INTO user_society_mapping (
    user_id,
    society_id,
    role,
    is_admin,
    is_member,
    permissions,
    flat_number,
    vehicles,
    family_members,
    move_in_date,
    ownership,
    is_approved,
    is_active,
    member_since,
    maintenance_status,
    joined_at
)
SELECT
    u.id,
    u.society_id,
    u.role,
    u.is_admin,
    u.is_member,
    u.permissions,
    u.flat_number,
    u.vehicles,
    u.family_members,
    u.move_in_date,
    u.ownership,
    u.is_approved,
    u.is_active,
    u.member_since,
    u.maintenance_status,
    u.created_at
FROM users u
WHERE u.role != 'superadmin'
  AND u.society_id IS NOT NULL
  AND u.status = 1;


-- ----------------------------------------------------------------------------
-- STEP 4: Update selected_society_id for existing users
--
-- Set selected_society_id = society_id for users who don't have one yet
-- (so the app doesn't break on first login after migration)
-- ----------------------------------------------------------------------------

UPDATE users
SET selected_society_id = society_id
WHERE selected_society_id IS NULL
  AND society_id IS NOT NULL
  AND role != 'superadmin';


-- ----------------------------------------------------------------------------
-- STEP 5: Drop old unique constraints that use society_id
-- and add global unique constraints
-- ----------------------------------------------------------------------------

-- Drop old composite unique constraints
ALTER TABLE users DROP INDEX uq_society_email;
ALTER TABLE users DROP INDEX uq_society_mobile;

-- Note: If there are duplicate emails/mobiles across societies,
-- you'll need to deduplicate them before adding these constraints.
-- Run this SELECT first to check:
--   SELECT email, COUNT(*) AS cnt FROM users WHERE status = 1 GROUP BY email HAVING cnt > 1;
--   SELECT mobile_number, COUNT(*) AS cnt FROM users WHERE status = 1 GROUP BY mobile_number HAVING cnt > 1;

-- Add global unique constraints (only if no duplicates exist)
-- ALTER TABLE users ADD UNIQUE KEY uq_email (email);
-- ALTER TABLE users ADD UNIQUE KEY uq_mobile (mobile_number);


-- ----------------------------------------------------------------------------
-- STEP 6: Drop old society-specific columns from users
-- 
-- ⚠️  Only run this AFTER verifying the migration data is correct!
--     Comment this out for the initial migration run.
-- ----------------------------------------------------------------------------

-- ALTER TABLE users DROP COLUMN society_id;
-- ALTER TABLE users DROP COLUMN role;
-- ALTER TABLE users DROP COLUMN flat_number;
-- ALTER TABLE users DROP COLUMN vehicles;
-- ALTER TABLE users DROP COLUMN family_members;
-- ALTER TABLE users DROP COLUMN move_in_date;
-- ALTER TABLE users DROP COLUMN ownership;
-- ALTER TABLE users DROP COLUMN is_approved;
-- ALTER TABLE users DROP COLUMN is_admin;
-- ALTER TABLE users DROP COLUMN is_member;
-- ALTER TABLE users DROP COLUMN permissions;
-- ALTER TABLE users DROP COLUMN maintenance_status;
-- ALTER TABLE users DROP COLUMN member_since;


-- ============================================================================
-- ROLLBACK (if needed)
-- ============================================================================
-- DROP TABLE IF EXISTS user_society_mapping;
-- ALTER TABLE users DROP COLUMN is_superadmin;