USE ggn_famlynk;

INSERT INTO families (id, family_code, name, status, is_enabled, created_by, updated_by)
VALUES
  (1, 'SYSTEM', 'System Tenant', 'active', 1, 1, 1),
  (2, 'FAM-001', 'Johnson Family', 'active', 1, 1, 1),
  (3, 'FAM-002', 'Smith Family', 'pending', 0, 1, 1)
ON DUPLICATE KEY UPDATE name = VALUES(name), status = VALUES(status), is_enabled = VALUES(is_enabled);

INSERT INTO users (id, family_id, name, email, phone, password_hash, status, is_super_admin, created_by, updated_by)
VALUES
  (1, 1, 'Super Admin', 'admin@famlynk.com', '+1-000-000-0000', '$2b$12$mW4YfKOr6hTc1r4rK4AA8e2I30E8f0YJz9XW0oVJxK2f8N6dQhL7u', 'active', 1, 1, 1),
  (2, 2, 'John Johnson', 'john@johnson.com', '+1-555-0101', '$2b$12$mW4YfKOr6hTc1r4rK4AA8e2I30E8f0YJz9XW0oVJxK2f8N6dQhL7u', 'active', 0, 1, 1),
  (3, 2, 'Sarah Johnson', 'sarah@johnson.com', '+1-555-0102', '$2b$12$mW4YfKOr6hTc1r4rK4AA8e2I30E8f0YJz9XW0oVJxK2f8N6dQhL7u', 'active', 0, 2, 2),
  (4, 2, 'Tommy Johnson', 'tommy@johnson.com', '+1-555-0103', '$2b$12$mW4YfKOr6hTc1r4rK4AA8e2I30E8f0YJz9XW0oVJxK2f8N6dQhL7u', 'active', 0, 2, 2)
ON DUPLICATE KEY UPDATE name = VALUES(name), phone = VALUES(phone), status = VALUES(status);

INSERT INTO roles (id, family_id, code, name, is_system, created_by, updated_by)
VALUES
  (1, 1, 'super_admin', 'Super Admin', 1, 1, 1),
  (2, 1, 'family_admin', 'Family Admin', 1, 1, 1),
  (3, 1, 'member', 'Member', 1, 1, 1),
  (4, 1, 'child', 'Child', 1, 1, 1),
  (5, 2, 'family_admin', 'Family Admin', 1, 2, 2),
  (6, 2, 'member', 'Member', 1, 2, 2),
  (7, 2, 'child', 'Child', 1, 2, 2)
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO permissions (id, module, action, code, description)
VALUES
  (1, 'finance', 'create_transaction', 'finance:create_transaction', 'Create income/expense entries'),
  (2, 'finance', 'approve_transaction', 'finance:approve_transaction', 'Approve or reject transactions'),
  (3, 'tasks', 'manage', 'tasks:manage', 'Manage tasks and comments'),
  (4, 'shopping', 'manage', 'shopping:manage', 'Manage shopping lists'),
  (5, 'reports', 'read', 'reports:read', 'View reports')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO user_roles (family_id, user_id, role_id, created_by, updated_by)
VALUES
  (1, 1, 1, 1, 1),
  (2, 2, 5, 2, 2),
  (2, 3, 6, 2, 2),
  (2, 4, 7, 2, 2)
ON DUPLICATE KEY UPDATE updated_by = VALUES(updated_by);

INSERT INTO transactions (family_id, member_id, txn_type, amount, category, notes, txn_date, status, created_by, updated_by)
VALUES
  (2, 2, 'income', 5500.00, 'Salary', 'Monthly salary', '2026-03-01', 'approved', 2, 2),
  (2, 2, 'expense', 1250.00, 'Groceries', 'Weekly grocery shopping', '2026-03-01', 'approved', 2, 2),
  (2, 3, 'expense', 450.00, 'Utilities', 'Electricity bill', '2026-03-02', 'pending', 3, 3);

INSERT INTO approvals (family_id, request_type, request_id, requested_by, status, created_by, updated_by)
VALUES
  (2, 'transaction', 3, 3, 'pending', 3, 3);

INSERT INTO loans (family_id, member_id, bank_name, interest_type, interest_rate, tenure_months, principal_amount, outstanding_balance, emi_amount, start_date, created_by, updated_by)
VALUES
  (2, 2, 'HDFC Bank', 'diminishing', 8.500, 240, 2500000.00, 1850000.00, 19500.00, '2022-06-01', 2, 2);

INSERT INTO task_board_columns (family_id, name, code, sort_order, is_system, created_by, updated_by)
VALUES
  (2, 'To Do', 'todo', 1, 1, 2, 2),
  (2, 'In Progress', 'inprogress', 2, 1, 2, 2),
  (2, 'Done', 'done', 3, 1, 2, 2)
ON DUPLICATE KEY UPDATE name = VALUES(name), sort_order = VALUES(sort_order);

INSERT INTO tasks (family_id, column_id, title, description, assigned_to, due_date, created_by, updated_by)
VALUES
  (2, (SELECT id FROM task_board_columns WHERE family_id=2 AND code='todo' LIMIT 1), 'Pay electricity bill', 'Due date: March 10, 2026', 3, '2026-03-10', 2, 2);

INSERT INTO shopping_lists (family_id, name, is_active, created_by, updated_by)
VALUES
  (2, 'Weekly Groceries', 1, 2, 2);

INSERT INTO shopping_items (family_id, list_id, item_name, quantity, category, assigned_member_id, purchase_date, status, created_by, updated_by)
VALUES
  (2, (SELECT id FROM shopping_lists WHERE family_id=2 AND name='Weekly Groceries' LIMIT 1), 'Milk', 2, 'Dairy', 2, '2026-03-06', 'to_buy', 3, 3),
  (2, (SELECT id FROM shopping_lists WHERE family_id=2 AND name='Weekly Groceries' LIMIT 1), 'Bread', 2, 'Bakery', NULL, NULL, 'purchased', 3, 3);

INSERT INTO calendar_events (family_id, title, event_type, event_date, event_time, reminder_enabled, created_by, updated_by)
VALUES
  (2, 'Credit Card Payment Due', 'bill', '2026-03-10', NULL, 1, 2, 2),
  (2, 'Tommy\'s Birthday', 'birthday', '2026-03-15', NULL, 1, 2, 2);

INSERT INTO emergency_contacts (family_id, name, phone, relationship, email, is_emergency, created_by, updated_by)
VALUES
  (2, 'Dr. Smith', '+1-555-1234', 'Family Doctor', 'dr.smith@clinic.com', 1, 2, 2);

INSERT INTO devices (family_id, name, device_type, room_name, status, created_by, updated_by)
VALUES
  (2, 'Living Room Light', 'light', 'Living Room', 'on', 2, 2),
  (2, 'Bedroom Fan', 'fan', 'Bedroom', 'off', 2, 2);

INSERT INTO automation_rules (family_id, name, condition_expression, action_expression, priority_order, is_enabled, created_by, updated_by)
VALUES
  (2, 'Night Safety', 'no_member_near_home == true AND time >= "22:00"', 'turn_off_lights(); lock_doors();', 1, 1, 2, 2);

INSERT INTO entities (family_id, entity_type, entity_ref_id, created_by, updated_by)
VALUES
  (2, 'member', 3, 2, 2),
  (2, 'device', 1, 2, 2);

INSERT INTO attributes (family_id, entity_type, attribute_code, data_type, is_required, created_by, updated_by)
VALUES
  (2, 'member', 'allergy', 'text', 0, 2, 2),
  (2, 'member', 'insurance_policy', 'text', 0, 2, 2),
  (2, 'device', 'brightness', 'number', 0, 2, 2),
  (2, 'automation_rule', 'priority_tag', 'text', 0, 2, 2);

INSERT INTO entity_values (family_id, entity_id, attribute_id, value_text, value_number, created_by, updated_by)
VALUES
  (
    2,
    (SELECT id FROM entities WHERE family_id=2 AND entity_type='member' AND entity_ref_id=3 LIMIT 1),
    (SELECT id FROM attributes WHERE family_id=2 AND entity_type='member' AND attribute_code='allergy' LIMIT 1),
    'Peanut',
    NULL,
    2,
    2
  ),
  (
    2,
    (SELECT id FROM entities WHERE family_id=2 AND entity_type='device' AND entity_ref_id=1 LIMIT 1),
    (SELECT id FROM attributes WHERE family_id=2 AND entity_type='device' AND attribute_code='brightness' LIMIT 1),
    NULL,
    80,
    2,
    2
  );