<?php

class Migration_20240712000010_create_property_users_tbl
{
    public static function up(PDO $pdo)
    {
        $sql = "

            DROP TABLE IF EXISTS audit_trail_login;
            DROP TABLE IF EXISTS property_users_data;
            DROP TABLE IF EXISTS profile_photo;
            DROP TABLE IF EXISTS property_users;
            DROP TABLE IF EXISTS property_groups;       
        
            CREATE TABLE IF NOT EXISTS property_groups (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL,
                property_id INT NOT NULL,
                created_by INT NOT NULL,
                creator_type ENUM('users', 'property_users'),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (property_id) REFERENCES properties(id)
            );

            CREATE TABLE IF NOT EXISTS property_users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                email VARCHAR(100) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                role INT NOT NULL,
                role_type INT NOT NULL,
                status ENUM('registered', 'not_registered') NOT NULL,
                property_id INT NOT NULL,
                created_by INT NOT NULL,
                creator_type ENUM('users', 'property_users'),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (property_id) REFERENCES properties(id)
            );

            CREATE TABLE IF NOT EXISTS profile_photo (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                location VARCHAR(255) NOT NULL,
                created_by INT NOT NULL,
                creator_type ENUM('users', 'property_users'),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES property_users(id)
            );

            CREATE TABLE IF NOT EXISTS property_users_data (
                id INT AUTO_INCREMENT PRIMARY KEY,
                property_user INT NOT NULL,
                firstname VARCHAR(255) NOT NULL,
                lastname VARCHAR(255) NOT NULL,
                unit VARCHAR(10) NOT NULL,
                street INT NOT NULL,
                mobile_number VARCHAR(10) NOT NULL,
                home_number VARCHAR(10) NOT NULL,
                role INT NOT NULL,
                custom INT NOT NULL,
                created_by INT NOT NULL,
                creator_type ENUM('users', 'property_users'),
                property_id INT NOT NULL,
                photo_loc INT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (street) REFERENCES property_groups(id),
                FOREIGN KEY (property_id) REFERENCES properties(id),
                FOREIGN KEY (photo_loc) REFERENCES profile_photo(id)
            );

            CREATE TABLE IF NOT EXISTS audit_trail_login (
                id INT AUTO_INCREMENT PRIMARY KEY,
                global_user_id INT,
                property_user_id INT,
                property_id INT,
                logged_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (global_user_id) REFERENCES users(id),
                FOREIGN KEY (property_user_id) REFERENCES property_users(id),
                FOREIGN KEY (property_id) REFERENCES properties(id)
            );

        ";

        $pdo->exec($sql);
    }

    public static function down(PDO $pdo)
    {
        $sql = "
            DROP TABLE IF EXISTS audit_trail_login;
            DROP TABLE IF EXISTS property_users_data;
            DROP TABLE IF EXISTS profile_photo;
            DROP TABLE IF EXISTS property_users;
            DROP TABLE IF EXISTS property_groups;
        ";

        $pdo->exec($sql);
    }
}
