CREATE DATABASE IF NOT EXISTS clubix
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE clubix;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS parent_children;
DROP TABLE IF EXISTS parent_messages;
DROP TABLE IF EXISTS member_accounts;
DROP TABLE IF EXISTS parent_accounts;
DROP TABLE IF EXISTS subscription_billing_entries;
DROP TABLE IF EXISTS organization_subscriptions;
DROP TABLE IF EXISTS subscription_plans;
DROP TABLE IF EXISTS user_tokens;
DROP TABLE IF EXISTS push_devices;
DROP TABLE IF EXISTS login_attempts;
DROP TABLE IF EXISTS cron_log;
DROP TABLE IF EXISTS membership_reminders;
DROP TABLE IF EXISTS email_log;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS notification_rules;
DROP TABLE IF EXISTS notification_templates;
DROP TABLE IF EXISTS expenses;
DROP TABLE IF EXISTS equipment_assignments;
DROP TABLE IF EXISTS equipment;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS membership_fees;
DROP TABLE IF EXISTS instructor_groups;
DROP TABLE IF EXISTS instructors;
DROP TABLE IF EXISTS group_members;
DROP TABLE IF EXISTS `groups`;
DROP TABLE IF EXISTS member_parents;
DROP TABLE IF EXISTS parents;
DROP TABLE IF EXISTS members;
DROP TABLE IF EXISTS user_roles;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS locations;
DROP TABLE IF EXISTS organization_settings;
DROP TABLE IF EXISTS organizations;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE user_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  authenticatable_id BIGINT UNSIGNED NOT NULL,
  guard ENUM('internal', 'parent', 'member') NOT NULL DEFAULT 'internal',
  token_hash CHAR(64) NOT NULL,
  last_used_at DATETIME NULL,
  expires_at DATETIME NULL,
  revoked_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_tokens_hash (token_hash),
  KEY idx_user_tokens_auth (guard, authenticatable_id),
  KEY idx_user_tokens_org (organization_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE push_devices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  guard ENUM('internal', 'parent', 'member') NOT NULL DEFAULT 'internal',
  authenticatable_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  parent_account_id BIGINT UNSIGNED NULL,
  member_account_id BIGINT UNSIGNED NULL,
  platform ENUM('ios', 'android', 'web') NOT NULL DEFAULT 'web',
  provider ENUM('fcm', 'apns', 'webpush', 'unknown') NOT NULL DEFAULT 'unknown',
  device_uuid VARCHAR(190) NOT NULL,
  device_name VARCHAR(190) NULL,
  device_model VARCHAR(190) NULL,
  app_version VARCHAR(50) NULL,
  os_version VARCHAR(50) NULL,
  locale VARCHAR(20) NULL,
  push_token VARCHAR(255) NOT NULL,
  notifications_enabled TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_push_devices_actor_device (organization_id, guard, authenticatable_id, device_uuid),
  KEY idx_push_devices_token (push_token),
  KEY idx_push_devices_actor (organization_id, guard, authenticatable_id),
  KEY idx_push_devices_active (organization_id, revoked_at, notifications_enabled),
  KEY idx_push_devices_platform (platform, provider)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE login_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  ip VARCHAR(64) NOT NULL,
  guard ENUM('internal', 'parent', 'member') NOT NULL DEFAULT 'internal',
  attempted_email VARCHAR(190) NULL,
  attempted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  success TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  KEY idx_login_attempts_ip_guard (ip, guard, attempted_at),
  KEY idx_login_attempts_success (success, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE organizations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  legal_name VARCHAR(200) NULL,
  slug VARCHAR(120) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NULL,
  tax_id VARCHAR(100) NULL,
  address_line_1 VARCHAR(190) NULL,
  address_line_2 VARCHAR(190) NULL,
  city VARCHAR(120) NULL,
  state VARCHAR(120) NULL,
  postal_code VARCHAR(30) NULL,
  country_code CHAR(2) NULL,
  timezone VARCHAR(80) NOT NULL DEFAULT 'Europe/Belgrade',
  currency_code CHAR(3) NOT NULL DEFAULT 'EUR',
  status ENUM('active', 'inactive', 'suspended', 'trial') NOT NULL DEFAULT 'trial',
  trial_ends_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_organizations_slug (slug),
  KEY idx_organizations_status (status),
  KEY idx_organizations_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_plans (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(80) NOT NULL,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(255) NULL,
  max_members INT UNSIGNED NOT NULL DEFAULT 0,
  monthly_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  monthly_price_eur DECIMAL(10, 2) NULL DEFAULT NULL,
  currency_code CHAR(3) NOT NULL DEFAULT 'RSD',
  warning_threshold_percent TINYINT UNSIGNED NOT NULL DEFAULT 90,
  sort_order INT UNSIGNED NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_subscription_plans_code (code),
  KEY idx_subscription_plans_active (active, deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE organization_subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  subscription_plan_id BIGINT UNSIGNED NOT NULL,
  status ENUM('trial', 'pending', 'active', 'overdue', 'paused', 'cancelled', 'complimentary') NOT NULL DEFAULT 'active',
  starts_at DATE NULL,
  ends_at DATE NULL,
  price_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  currency_code CHAR(3) NOT NULL DEFAULT 'RSD',
  billing_mode ENUM('invoice_qr', 'online', 'complimentary') NOT NULL DEFAULT 'invoice_qr',
  trial_ends_at DATE NULL,
  next_billing_at DATE NULL,
  last_paid_at DATE NULL,
  billing_grace_days INT UNSIGNED NOT NULL DEFAULT 3,
  member_limit_override INT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_organization_subscriptions_org (organization_id, deleted_at),
  KEY idx_organization_subscriptions_plan (subscription_plan_id),
  CONSTRAINT fk_organization_subscriptions_org
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_organization_subscriptions_plan
    FOREIGN KEY (subscription_plan_id) REFERENCES subscription_plans (id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE subscription_billing_entries (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  organization_subscription_id BIGINT UNSIGNED NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  due_date DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  currency_code CHAR(3) NOT NULL DEFAULT 'RSD',
  status ENUM('pending', 'paid', 'overdue', 'cancelled', 'complimentary') NOT NULL DEFAULT 'pending',
  billing_mode ENUM('invoice_qr', 'online', 'complimentary') NOT NULL DEFAULT 'invoice_qr',
  reference_code VARCHAR(120) NOT NULL,
  paid_at DATE NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_subscription_billing_period (organization_subscription_id, due_date),
  UNIQUE KEY uq_subscription_billing_reference (reference_code),
  KEY idx_subscription_billing_org_status (organization_id, status, due_date),
  KEY idx_subscription_billing_due (due_date, status),
  CONSTRAINT fk_subscription_billing_entries_org
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_subscription_billing_entries_subscription
    FOREIGN KEY (organization_subscription_id) REFERENCES organization_subscriptions (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE organization_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  app_name VARCHAR(150) NULL,
  logo_url VARCHAR(255) NULL,
  primary_color VARCHAR(20) NULL,
  secondary_color VARCHAR(20) NULL,
  payment_due_day TINYINT UNSIGNED NULL,
  locale VARCHAR(20) NOT NULL DEFAULT 'sr_RS',
  timezone VARCHAR(80) NOT NULL DEFAULT 'Europe/Belgrade',
  currency_code CHAR(3) NOT NULL DEFAULT 'EUR',
  date_format VARCHAR(20) NOT NULL DEFAULT 'd.m.Y',
  notification_email_from VARCHAR(190) NULL,
  notification_sms_from VARCHAR(50) NULL,
  bank_account VARCHAR(80) NULL,
  payment_model VARCHAR(20) NULL,
  payment_reference_template VARCHAR(190) NULL,
  payment_recipient_name VARCHAR(190) NULL,
  default_payment_purpose VARCHAR(190) NULL,
  default_membership_fee DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  opening_balance_amount DECIMAL(12, 2) NOT NULL DEFAULT 0.00,
  opening_balance_date DATE NULL,
  opening_balance_note VARCHAR(255) NULL,
  membership_due_day TINYINT UNSIGNED NOT NULL DEFAULT 10,
  notify_after_days INT UNSIGNED NOT NULL DEFAULT 7,
  enable_parent_portal TINYINT(1) NOT NULL DEFAULT 1,
  enable_attendance_module TINYINT(1) NOT NULL DEFAULT 1,
  enable_equipment_module TINYINT(1) NOT NULL DEFAULT 1,
  portal_enabled TINYINT(1) NOT NULL DEFAULT 1,
  messaging_enabled TINYINT(1) NOT NULL DEFAULT 0,
  messaging_channels VARCHAR(190) NULL,
  reminders_enabled TINYINT(1) NOT NULL DEFAULT 1,
  reminder_days_overdue INT UNSIGNED NOT NULL DEFAULT 3,
  reminder_days_before INT UNSIGNED NOT NULL DEFAULT 3,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_organization_settings_org (organization_id),
  UNIQUE KEY uq_organization_settings_org_id (organization_id, id),
  KEY idx_organization_settings_status (status),
  CONSTRAINT fk_organization_settings_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE locations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(60) NULL,
  address_line_1 VARCHAR(190) NULL,
  address_line_2 VARCHAR(190) NULL,
  city VARCHAR(120) NULL,
  state VARCHAR(120) NULL,
  postal_code VARCHAR(30) NULL,
  country_code CHAR(2) NULL,
  phone VARCHAR(50) NULL,
  email VARCHAR(190) NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_locations_org_code (organization_id, code),
  UNIQUE KEY uq_locations_org_id (organization_id, id),
  KEY idx_locations_org_status (organization_id, status),
  KEY idx_locations_deleted_at (deleted_at),
  CONSTRAINT fk_locations_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL,
  phone VARCHAR(50) NULL,
  password_hash VARCHAR(255) NOT NULL,
  last_login_at DATETIME NULL,
  status ENUM('active', 'inactive', 'invited', 'locked') NOT NULL DEFAULT 'invited',
  password_reset_token VARCHAR(190) NULL,
  password_reset_expires_at DATETIME NULL,
  password_reset_sent_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_org_email (organization_id, email),
  UNIQUE KEY uq_users_org_id (organization_id, id),
  KEY idx_users_org_status (organization_id, status),
  KEY idx_users_location (organization_id, location_id),
  KEY idx_users_deleted_at (deleted_at),
  CONSTRAINT fk_users_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_users_location
    FOREIGN KEY (organization_id, location_id) REFERENCES locations (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  code VARCHAR(80) NOT NULL,
  description VARCHAR(255) NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_org_code (organization_id, code),
  UNIQUE KEY uq_roles_org_name (organization_id, name),
  UNIQUE KEY uq_roles_org_id (organization_id, id),
  KEY idx_roles_org_status (organization_id, status),
  KEY idx_roles_deleted_at (deleted_at),
  CONSTRAINT fk_roles_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE user_roles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_user_roles_org_user_role (organization_id, user_id, role_id),
  KEY idx_user_roles_role (organization_id, role_id),
  CONSTRAINT fk_user_roles_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_user_roles_user
    FOREIGN KEY (organization_id, user_id) REFERENCES users (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_user_roles_role
    FOREIGN KEY (organization_id, role_id) REFERENCES roles (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  member_number VARCHAR(80) NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  date_of_birth DATE NULL,
  gender ENUM('male', 'female', 'other', 'unspecified') NOT NULL DEFAULT 'unspecified',
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NULL,
  emergency_contact_name VARCHAR(150) NULL,
  emergency_contact_phone VARCHAR(50) NULL,
  medical_notes TEXT NULL,
  joined_at DATE NULL,
  status ENUM('active', 'inactive', 'paused', 'archived') NOT NULL DEFAULT 'active',
  membership_fee DECIMAL(10,2) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_members_org_member_number (organization_id, member_number),
  UNIQUE KEY uq_members_org_id (organization_id, id),
  KEY idx_members_org_status (organization_id, status),
  KEY idx_members_location (organization_id, location_id),
  KEY idx_members_name (organization_id, last_name, first_name),
  KEY idx_members_deleted_at (deleted_at),
  CONSTRAINT fk_members_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_members_location
    FOREIGN KEY (organization_id, location_id) REFERENCES locations (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  relationship_type ENUM('mother', 'father', 'guardian', 'other') NOT NULL DEFAULT 'guardian',
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NULL,
  alternate_phone VARCHAR(50) NULL,
  address_line_1 VARCHAR(190) NULL,
  address_line_2 VARCHAR(190) NULL,
  city VARCHAR(120) NULL,
  state VARCHAR(120) NULL,
  postal_code VARCHAR(30) NULL,
  country_code CHAR(2) NULL,
  status ENUM('active', 'inactive', 'blocked') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_parents_org_id (organization_id, id),
  KEY idx_parents_org_status (organization_id, status),
  KEY idx_parents_email (organization_id, email),
  KEY idx_parents_phone (organization_id, phone),
  KEY idx_parents_deleted_at (deleted_at),
  CONSTRAINT fk_parents_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_parents (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NOT NULL,
  relationship_label VARCHAR(80) NULL,
  is_primary_contact TINYINT(1) NOT NULL DEFAULT 0,
  receives_notifications TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_member_parents_org_member_parent (organization_id, member_id, parent_id),
  KEY idx_member_parents_parent (organization_id, parent_id),
  CONSTRAINT fk_member_parents_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_member_parents_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_member_parents_parent
    FOREIGN KEY (organization_id, parent_id) REFERENCES parents (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `groups` (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(80) NULL,
  age_from TINYINT UNSIGNED NULL,
  age_to TINYINT UNSIGNED NULL,
  capacity INT UNSIGNED NULL,
  schedule_summary VARCHAR(255) NULL,
  status ENUM('active', 'inactive', 'archived') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_groups_org_code (organization_id, code),
  UNIQUE KEY uq_groups_org_id (organization_id, id),
  KEY idx_groups_org_status (organization_id, status),
  KEY idx_groups_location (organization_id, location_id),
  KEY idx_groups_deleted_at (deleted_at),
  CONSTRAINT fk_groups_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_groups_location
    FOREIGN KEY (organization_id, location_id) REFERENCES locations (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE group_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  group_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  joined_at DATE NULL,
  left_at DATE NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  is_primary TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active', 'inactive', 'completed') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_group_members_org_group_member (organization_id, group_id, member_id),
  KEY idx_group_members_member (organization_id, member_id),
  KEY idx_group_members_status (organization_id, status),
  CONSTRAINT fk_group_members_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_group_members_group
    FOREIGN KEY (organization_id, group_id) REFERENCES `groups` (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_group_members_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE instructors (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NULL,
  first_name VARCHAR(100) NOT NULL,
  last_name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NULL,
  phone VARCHAR(50) NULL,
  specialization VARCHAR(190) NULL,
  hire_date DATE NULL,
  status ENUM('active', 'inactive', 'external') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_instructors_org_id (organization_id, id),
  KEY idx_instructors_org_status (organization_id, status),
  KEY idx_instructors_user (organization_id, user_id),
  KEY idx_instructors_location (organization_id, location_id),
  KEY idx_instructors_deleted_at (deleted_at),
  CONSTRAINT fk_instructors_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_instructors_user
    FOREIGN KEY (organization_id, user_id) REFERENCES users (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_instructors_location
    FOREIGN KEY (organization_id, location_id) REFERENCES locations (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE instructor_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  instructor_id BIGINT UNSIGNED NOT NULL,
  group_id BIGINT UNSIGNED NOT NULL,
  role_in_group ENUM('lead', 'assistant', 'substitute') NOT NULL DEFAULT 'lead',
  assigned_from DATE NULL,
  assigned_to DATE NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_instructor_groups_org_instructor_group (organization_id, instructor_id, group_id),
  KEY idx_instructor_groups_group (organization_id, group_id),
  CONSTRAINT fk_instructor_groups_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_instructor_groups_instructor
    FOREIGN KEY (organization_id, instructor_id) REFERENCES instructors (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_instructor_groups_group
    FOREIGN KEY (organization_id, group_id) REFERENCES `groups` (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendance_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  group_id BIGINT UNSIGNED NOT NULL,
  instructor_id BIGINT UNSIGNED NULL,
  session_date DATE NOT NULL,
  start_time TIME NULL,
  notes VARCHAR(500) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_attendance_sessions_org_group_date (organization_id, group_id, session_date),
  UNIQUE KEY uq_attendance_sessions_org_id (organization_id, id),
  KEY idx_attendance_sessions_org_date (organization_id, session_date),
  KEY idx_attendance_sessions_instructor (organization_id, instructor_id),
  CONSTRAINT fk_attendance_sessions_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_attendance_sessions_group
    FOREIGN KEY (organization_id, group_id) REFERENCES `groups` (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_attendance_sessions_instructor
    FOREIGN KEY (organization_id, instructor_id) REFERENCES instructors (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_attendance_sessions_created_by
    FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE attendance_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  session_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  status ENUM('present', 'absent', 'late', 'excused') NOT NULL DEFAULT 'absent',
  note VARCHAR(300) NULL,
  marked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_attendance_records_org_session_member (organization_id, session_id, member_id),
  KEY idx_attendance_records_member (organization_id, member_id),
  KEY idx_attendance_records_status (organization_id, status),
  CONSTRAINT fk_attendance_records_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_attendance_records_session
    FOREIGN KEY (organization_id, session_id) REFERENCES attendance_sessions (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_attendance_records_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE membership_fees (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  group_id BIGINT UNSIGNED NULL,
  location_id BIGINT UNSIGNED NULL,
  month TINYINT UNSIGNED NOT NULL,
  year SMALLINT UNSIGNED NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  discount_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  paid_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  status ENUM('unpaid', 'partial', 'paid', 'cancelled') NOT NULL DEFAULT 'unpaid',
  due_date DATE NOT NULL,
  issued_at DATE NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_membership_fees_org_member_period (organization_id, member_id, year, month),
  UNIQUE KEY uq_membership_fees_org_id (organization_id, id),
  KEY idx_membership_fees_org_status (organization_id, status),
  KEY idx_membership_fees_group (organization_id, group_id),
  KEY idx_membership_fees_due_date (organization_id, due_date),
  KEY idx_membership_fees_location (organization_id, location_id),
  KEY idx_membership_fees_deleted_at (deleted_at),
  CONSTRAINT chk_membership_fees_month CHECK (month BETWEEN 1 AND 12),
  CONSTRAINT fk_membership_fees_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_membership_fees_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_membership_fees_group
    FOREIGN KEY (organization_id, group_id) REFERENCES `groups` (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_membership_fees_location
    FOREIGN KEY (organization_id, location_id) REFERENCES locations (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  membership_fee_id BIGINT UNSIGNED NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  received_by_user_id BIGINT UNSIGNED NULL,
  payment_date DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  method ENUM('cash', 'card', 'bank_transfer', 'online', 'other') NOT NULL DEFAULT 'cash',
  reference_number VARCHAR(120) NULL,
  status ENUM('pending', 'completed', 'failed', 'refunded', 'cancelled') NOT NULL DEFAULT 'completed',
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_payments_org_id (organization_id, id),
  KEY idx_payments_member (organization_id, member_id),
  KEY idx_payments_fee (organization_id, membership_fee_id),
  KEY idx_payments_parent (organization_id, parent_id),
  KEY idx_payments_user (organization_id, received_by_user_id),
  KEY idx_payments_status_date (organization_id, status, payment_date),
  KEY idx_payments_deleted_at (deleted_at),
  CONSTRAINT fk_payments_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_payments_membership_fee
    FOREIGN KEY (organization_id, membership_fee_id) REFERENCES membership_fees (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_payments_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_payments_parent
    FOREIGN KEY (organization_id, parent_id) REFERENCES parents (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_payments_user
    FOREIGN KEY (organization_id, received_by_user_id) REFERENCES users (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE equipment (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  sku VARCHAR(100) NULL,
  name VARCHAR(150) NOT NULL,
  category VARCHAR(100) NULL,
  size VARCHAR(50) NULL,
  condition_status ENUM('new', 'excellent', 'good', 'fair', 'damaged', 'retired') NOT NULL DEFAULT 'good',
  quantity INT UNSIGNED NOT NULL DEFAULT 1,
  available_quantity INT UNSIGNED NOT NULL DEFAULT 1,
  status ENUM('active', 'inactive', 'out_of_stock', 'archived') NOT NULL DEFAULT 'active',
  purchase_date DATE NULL,
  purchase_price DECIMAL(10, 2) NULL,
  note VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_equipment_org_sku (organization_id, sku),
  UNIQUE KEY uq_equipment_org_id (organization_id, id),
  KEY idx_equipment_org_status (organization_id, status),
  KEY idx_equipment_location (organization_id, location_id),
  KEY idx_equipment_condition (organization_id, condition_status),
  KEY idx_equipment_deleted_at (deleted_at),
  CONSTRAINT fk_equipment_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_equipment_location
    FOREIGN KEY (organization_id, location_id) REFERENCES locations (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT chk_equipment_available_quantity CHECK (available_quantity <= quantity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE equipment_assignments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  equipment_id BIGINT UNSIGNED NOT NULL,
  assigned_by_user_id BIGINT UNSIGNED NULL,
  assigned_at DATETIME NOT NULL,
  expected_return_at DATETIME NULL,
  returned_at DATETIME NULL,
  status ENUM('assigned', 'returned', 'overdue', 'lost', 'damaged', 'cancelled') NOT NULL DEFAULT 'assigned',
  condition_on_assign VARCHAR(100) NULL,
  condition_on_return VARCHAR(100) NULL,
  note TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_equipment_assignments_org_id (organization_id, id),
  KEY idx_equipment_assignments_member (organization_id, member_id),
  KEY idx_equipment_assignments_equipment (organization_id, equipment_id),
  KEY idx_equipment_assignments_status (organization_id, status),
  KEY idx_equipment_assignments_due (organization_id, expected_return_at),
  KEY idx_equipment_assignments_user (organization_id, assigned_by_user_id),
  KEY idx_equipment_assignments_deleted_at (deleted_at),
  CONSTRAINT fk_equipment_assignments_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_equipment_assignments_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_equipment_assignments_equipment
    FOREIGN KEY (organization_id, equipment_id) REFERENCES equipment (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_equipment_assignments_user
    FOREIGN KEY (organization_id, assigned_by_user_id) REFERENCES users (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE expenses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  location_id BIGINT UNSIGNED NULL,
  created_by_user_id BIGINT UNSIGNED NULL,
  category VARCHAR(100) NOT NULL,
  expense_date DATE NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  vendor_name VARCHAR(150) NULL,
  payment_method ENUM('cash', 'card', 'bank_transfer', 'online', 'other') NOT NULL DEFAULT 'cash',
  reference_number VARCHAR(120) NULL,
  description VARCHAR(255) NULL,
  status ENUM('draft', 'approved', 'paid', 'cancelled') NOT NULL DEFAULT 'draft',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_expenses_org_id (organization_id, id),
  KEY idx_expenses_location (organization_id, location_id),
  KEY idx_expenses_user (organization_id, created_by_user_id),
  KEY idx_expenses_status_date (organization_id, status, expense_date),
  KEY idx_expenses_deleted_at (deleted_at),
  CONSTRAINT fk_expenses_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_expenses_location
    FOREIGN KEY (organization_id, location_id) REFERENCES locations (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_expenses_user
    FOREIGN KEY (organization_id, created_by_user_id) REFERENCES users (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_templates (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(80) NOT NULL,
  channel ENUM('email', 'sms', 'push', 'in_app', 'viber', 'whatsapp') NOT NULL DEFAULT 'email',
  subject VARCHAR(190) NULL,
  body TEXT NOT NULL,
  variables_json JSON NULL,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_notification_templates_org_code (organization_id, code),
  UNIQUE KEY uq_notification_templates_org_id (organization_id, id),
  KEY idx_notification_templates_status (organization_id, status),
  KEY idx_notification_templates_deleted_at (deleted_at),
  CONSTRAINT fk_notification_templates_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notification_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  template_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  trigger_type ENUM('membership_due', 'membership_overdue', 'group_change', 'equipment_overdue', 'manual') NOT NULL DEFAULT 'manual',
  trigger_offset_days INT NOT NULL DEFAULT 0,
  audience ENUM('member', 'parent', 'instructor', 'user') NOT NULL DEFAULT 'parent',
  channel ENUM('email', 'sms', 'push', 'in_app', 'viber', 'whatsapp') NOT NULL DEFAULT 'email',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_notification_rules_org_id (organization_id, id),
  KEY idx_notification_rules_template (organization_id, template_id),
  KEY idx_notification_rules_status (organization_id, status, is_active),
  KEY idx_notification_rules_deleted_at (deleted_at),
  CONSTRAINT fk_notification_rules_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_notification_rules_template
    FOREIGN KEY (organization_id, template_id) REFERENCES notification_templates (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  template_id BIGINT UNSIGNED NULL,
  rule_id BIGINT UNSIGNED NULL,
  member_id BIGINT UNSIGNED NULL,
  parent_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  instructor_id BIGINT UNSIGNED NULL,
  channel ENUM('email', 'sms', 'push', 'in_app', 'viber', 'whatsapp') NOT NULL DEFAULT 'email',
  recipient VARCHAR(190) NOT NULL,
  subject VARCHAR(190) NULL,
  body TEXT NOT NULL,
  scheduled_at DATETIME NULL,
  sent_at DATETIME NULL,
  read_at DATETIME NULL,
  status ENUM('draft', 'queued', 'sent', 'failed', 'cancelled') NOT NULL DEFAULT 'draft',
  error_message VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_notifications_org_id (organization_id, id),
  KEY idx_notifications_template (organization_id, template_id),
  KEY idx_notifications_rule (organization_id, rule_id),
  KEY idx_notifications_member (organization_id, member_id),
  KEY idx_notifications_parent (organization_id, parent_id),
  KEY idx_notifications_user (organization_id, user_id),
  KEY idx_notifications_instructor (organization_id, instructor_id),
  KEY idx_notifications_status_scheduled (organization_id, status, scheduled_at),
  KEY idx_notifications_deleted_at (deleted_at),
  CONSTRAINT fk_notifications_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_notifications_template
    FOREIGN KEY (organization_id, template_id) REFERENCES notification_templates (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_notifications_rule
    FOREIGN KEY (organization_id, rule_id) REFERENCES notification_rules (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_notifications_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_notifications_parent
    FOREIGN KEY (organization_id, parent_id) REFERENCES parents (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_notifications_user
    FOREIGN KEY (organization_id, user_id) REFERENCES users (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT fk_notifications_instructor
    FOREIGN KEY (organization_id, instructor_id) REFERENCES instructors (organization_id, id)
    ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  recipient_email VARCHAR(190) NOT NULL,
  template VARCHAR(100) NOT NULL,
  subject VARCHAR(190) NOT NULL,
  status ENUM('sent', 'failed') NOT NULL DEFAULT 'sent',
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  error_message VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_email_log_org_sent (organization_id, sent_at),
  KEY idx_email_log_org_status (organization_id, status),
  CONSTRAINT fk_email_log_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE membership_reminders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  membership_fee_id BIGINT UNSIGNED NOT NULL,
  reminder_type ENUM('overdue', 'upcoming') NOT NULL DEFAULT 'overdue',
  recipient_email VARCHAR(190) NOT NULL,
  sent_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_membership_reminders_unique (organization_id, membership_fee_id, reminder_type, recipient_email),
  KEY idx_membership_reminders_sent (organization_id, reminder_type, sent_at),
  CONSTRAINT fk_membership_reminders_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_membership_reminders_fee
    FOREIGN KEY (organization_id, membership_fee_id) REFERENCES membership_fees (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cron_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  task VARCHAR(120) NOT NULL,
  started_at DATETIME NOT NULL,
  finished_at DATETIME NULL,
  status ENUM('running', 'success', 'failed') NOT NULL DEFAULT 'running',
  message VARCHAR(255) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_cron_log_task_started (task, started_at),
  KEY idx_cron_log_status (status, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parent_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  last_login_at DATETIME NULL,
  status ENUM('pending', 'active', 'inactive', 'locked') NOT NULL DEFAULT 'pending',
  email_verified_at DATETIME NULL,
  password_reset_token VARCHAR(190) NULL,
  password_reset_expires_at DATETIME NULL,
  password_reset_sent_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_parent_accounts_org_parent (organization_id, parent_id),
  UNIQUE KEY uq_parent_accounts_org_email (organization_id, email),
  UNIQUE KEY uq_parent_accounts_org_id (organization_id, id),
  KEY idx_parent_accounts_status (organization_id, status),
  KEY idx_parent_accounts_deleted_at (deleted_at),
  CONSTRAINT fk_parent_accounts_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_parent_accounts_parent
    FOREIGN KEY (organization_id, parent_id) REFERENCES parents (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE member_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  last_login_at DATETIME NULL,
  status ENUM('pending', 'active', 'inactive', 'locked') NOT NULL DEFAULT 'pending',
  email_verified_at DATETIME NULL,
  password_reset_token VARCHAR(190) NULL,
  password_reset_expires_at DATETIME NULL,
  password_reset_sent_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_member_accounts_org_member (organization_id, member_id),
  UNIQUE KEY uq_member_accounts_org_email (organization_id, email),
  UNIQUE KEY uq_member_accounts_org_id (organization_id, id),
  KEY idx_member_accounts_status (organization_id, status),
  KEY idx_member_accounts_deleted_at (deleted_at),
  CONSTRAINT fk_member_accounts_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_member_accounts_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parent_children (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  parent_account_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NOT NULL,
  relationship_label VARCHAR(80) NULL,
  can_view_finance TINYINT(1) NOT NULL DEFAULT 1,
  can_receive_notifications TINYINT(1) NOT NULL DEFAULT 1,
  status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_parent_children_org_account_member (organization_id, parent_account_id, member_id),
  KEY idx_parent_children_member (organization_id, member_id),
  KEY idx_parent_children_status (organization_id, status),
  CONSTRAINT fk_parent_children_organization
    FOREIGN KEY (organization_id) REFERENCES organizations (id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_parent_children_account
    FOREIGN KEY (organization_id, parent_account_id) REFERENCES parent_accounts (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_parent_children_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parent_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  organization_id BIGINT UNSIGNED NOT NULL,
  parent_account_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NOT NULL,
  member_id BIGINT UNSIGNED NULL,
  subject VARCHAR(190) NULL,
  body TEXT NOT NULL,
  status ENUM('new', 'read', 'archived') NOT NULL DEFAULT 'new',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_parent_messages_parent (organization_id, parent_account_id, created_at),
  KEY idx_parent_messages_member (organization_id, member_id),
  KEY idx_parent_messages_status (organization_id, status),
  CONSTRAINT fk_parent_messages_account
    FOREIGN KEY (organization_id, parent_account_id) REFERENCES parent_accounts (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_parent_messages_parent
    FOREIGN KEY (organization_id, parent_id) REFERENCES parents (organization_id, id)
    ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT fk_parent_messages_member
    FOREIGN KEY (organization_id, member_id) REFERENCES members (organization_id, id)
    ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;