-- -----------------------------------------------------
-- Table structure for table `{{TABLE_PREFIX}}_tenant_organisations`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_tenant_organisations` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `organisation_name` VARCHAR(255) NOT NULL,
  `category` VARCHAR(255) DEFAULT NULL,
  `localbody` VARCHAR(255) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `whatsapp` VARCHAR(20) DEFAULT NULL,
  `place` VARCHAR(255) DEFAULT NULL,
  `political_front` VARCHAR(50) DEFAULT NULL,
  `political_party` VARCHAR(50) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `location` TEXT DEFAULT NULL,
  `remark` TEXT DEFAULT NULL,
  `photo` TEXT DEFAULT NULL,
  `created_by` INT DEFAULT NULL,
  `created_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `modified_by` INT DEFAULT NULL,
  `modified_time` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `soft_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `deleted_time` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_{{TABLE_PREFIX}}_org_created_by`
    FOREIGN KEY (`created_by`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_users`(`id`)
    ON DELETE SET NULL,
  CONSTRAINT `fk_{{TABLE_PREFIX}}_org_modified_by`
    FOREIGN KEY (`modified_by`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_users`(`id`)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;




-- -----------------------------------------------------
-- Table structure for table `{{TABLE_PREFIX}}_tenant_organisation_bookmarks`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_tenant_organisation_bookmarks` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `organisation_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `bookmarked_on` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_org_user` (`organisation_id`, `user_id`),
  CONSTRAINT `fk_{{TABLE_PREFIX}}_org_bookmarks_org`
    FOREIGN KEY (`organisation_id`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_organisations`(`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_{{TABLE_PREFIX}}_org_bookmarks_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_users`(`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



-- -----------------------------------------------------
-- Table structure for table `{{TABLE_PREFIX}}_tenant_organisation_people`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_tenant_organisation_people` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `org_id` INT NOT NULL,
  `member_name` VARCHAR(100) NOT NULL,
  `profession` VARCHAR(100) DEFAULT NULL,
  `gender` VARCHAR(100) DEFAULT NULL,
  `religion` VARCHAR(100) DEFAULT NULL,
  `phone` VARCHAR(100) DEFAULT NULL,
  `whatsapp` VARCHAR(100) DEFAULT NULL,
  `political_front` VARCHAR(100) DEFAULT NULL,
  `political_party` VARCHAR(100) DEFAULT NULL,
  `localbody` VARCHAR(255) DEFAULT NULL,
  `place` VARCHAR(255) DEFAULT NULL,
  `member_status` VARCHAR(25) DEFAULT NULL,
  `remark` TEXT DEFAULT NULL,
  `photo` TEXT DEFAULT NULL,
  `created_by` INT DEFAULT NULL,
  `created_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `modified_by` INT DEFAULT NULL,
  `modified_time` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `soft_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `deleted_time` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_org_id` (`org_id`),
  CONSTRAINT `fk_{{TABLE_PREFIX}}_org_people_org`
    FOREIGN KEY (`org_id`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_organisations`(`id`)
    ON DELETE CASCADE,
  CONSTRAINT `fk_{{TABLE_PREFIX}}_org_people_created_by`
    FOREIGN KEY (`created_by`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_users`(`id`)
    ON DELETE SET NULL,
  CONSTRAINT `fk_{{TABLE_PREFIX}}_org_people_modified_by`
    FOREIGN KEY (`modified_by`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_users`(`id`)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;




