<?php

class Migration_20240712000001_roles_revamp
{
    public static function up(PDO $pdo)
    {
        $sql = "
            -- Resources Table
            CREATE TABLE IF NOT EXISTS resources (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL UNIQUE
            );

            -- Actions Table
            CREATE TABLE IF NOT EXISTS actions (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL UNIQUE
            );

            -- Users Table
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                firstname VARCHAR(50) NOT NULL,
                lastname VARCHAR(50) NOT NULL,
                password VARCHAR(255) NOT NULL,
                email VARCHAR(100) NOT NULL UNIQUE,
                layer ENUM('super_admin', 'admin', 'dealer', 'organization', 'property', 'user', 'visitor') NOT NULL,
                property_id INT DEFAULT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

            -- Organizations Table
            CREATE TABLE IF NOT EXISTS organizations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                address VARCHAR(255),
                city VARCHAR(100),
                state VARCHAR(50),
                zipcode VARCHAR(20),
                contact VARCHAR(100),
                contact_phone_number VARCHAR(20),
                dealer_id INT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(dealer_id) REFERENCES users(id)
            );

            -- Properties Table
            CREATE TABLE IF NOT EXISTS properties (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                address VARCHAR(255),
                city VARCHAR(100),
                state VARCHAR(50),
                zipcode VARCHAR(20),
                contact VARCHAR(100),
                contact_phone_number VARCHAR(20),
                organization_id INT,
                property_type ENUM('Residential', 'Multi-Family', 'Light-Commercial', 'Commercial') NOT NULL,
                demo_expiry_date DATE DEFAULT NULL,
                notes VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(organization_id) REFERENCES organizations(id)
            );

            -- Add foreign key to users table
            ALTER TABLE users ADD FOREIGN KEY (property_id) REFERENCES properties(id);

            -- Dealer Property Slots Table
            CREATE TABLE IF NOT EXISTS dealer_slots (
                dealer_id INT NOT NULL,
                slots INT NOT NULL DEFAULT 1,
                PRIMARY KEY(dealer_id),
                FOREIGN KEY(dealer_id) REFERENCES users(id)
            );

            -- Custom Roles Table
            CREATE TABLE IF NOT EXISTS roles (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL,
                created_by INT NOT NULL,
                FOREIGN KEY(created_by) REFERENCES users(id)
            );

            -- Role Resources Table
            CREATE TABLE IF NOT EXISTS role_resources (
                role_id INT NOT NULL,
                resource_id INT NOT NULL,
                action_id INT NOT NULL,
                PRIMARY KEY(role_id, resource_id, action_id),
                FOREIGN KEY(role_id) REFERENCES roles(id),
                FOREIGN KEY(resource_id) REFERENCES resources(id),
                FOREIGN KEY(action_id) REFERENCES actions(id)
            );

            -- Mapping Users to Roles
            CREATE TABLE IF NOT EXISTS user_roles (
                user_id INT NOT NULL,
                role_id INT NOT NULL,
                PRIMARY KEY(user_id, role_id),
                FOREIGN KEY(user_id) REFERENCES users(id),
                FOREIGN KEY(role_id) REFERENCES roles(id)
            );

            -- Associate Dealers and Properties
            CREATE TABLE IF NOT EXISTS dealer_properties (
                dealer_id INT NOT NULL,
                property_id INT NOT NULL,
                PRIMARY KEY (dealer_id, property_id),
                FOREIGN KEY (dealer_id) REFERENCES users(id),
                FOREIGN KEY (property_id) REFERENCES properties(id)
            );

            -- Invitations Table
            CREATE TABLE  IF NOT EXISTS invitations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                email VARCHAR(255) NOT NULL,
                token VARCHAR(64) NOT NULL,
                role varchar(25) NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";

        $pdo->exec($sql);
    }

    public static function down(PDO $pdo)
    {
        $sql = "
            DROP TABLE IF EXISTS dealer_properties;
            DROP TABLE IF EXISTS user_roles;
            DROP TABLE IF EXISTS role_resources;
            DROP TABLE IF EXISTS roles;
            DROP TABLE IF EXISTS dealer_slots;
            DROP TABLE IF EXISTS properties;
            DROP TABLE IF EXISTS organizations;
            DROP TABLE IF EXISTS users;
            DROP TABLE IF EXISTS actions;
            DROP TABLE IF EXISTS resources;
            DROP TABLE IF EXISTS invitations;
        ";

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