<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Migration_Create_klimaks_tables extends CI_Migration {

    public function up() {
        // Users table
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE,
                'auto_increment' => TRUE
            ),
            'username' => array(
                'type' => 'VARCHAR',
                'constraint' => '50',
                'unique' => TRUE
            ),
            'email' => array(
                'type' => 'VARCHAR',
                'constraint' => '255',
                'unique' => TRUE
            ),
            'password_hash' => array(
                'type' => 'VARCHAR',
                'constraint' => '255'
            ),
            'first_name' => array(
                'type' => 'VARCHAR',
                'constraint' => '100',
                'null' => TRUE
            ),
            'last_name' => array(
                'type' => 'VARCHAR',
                'constraint' => '100',
                'null' => TRUE
            ),
            'avatar' => array(
                'type' => 'VARCHAR',
                'constraint' => '255',
                'null' => TRUE
            ),
            'bio' => array(
                'type' => 'TEXT',
                'null' => TRUE
            ),
            'role' => array(
                'type' => 'ENUM',
                'constraint' => array('guest', 'user', 'creator', 'admin'),
                'default' => 'user'
            ),
            'is_verified' => array(
                'type' => 'BOOLEAN',
                'default' => FALSE
            ),
            'is_banned' => array(
                'type' => 'BOOLEAN',
                'default' => FALSE
            ),
            'birth_date' => array(
                'type' => 'DATE',
                'null' => TRUE
            ),
            'country' => array(
                'type' => 'VARCHAR',
                'constraint' => '100',
                'null' => TRUE
            ),
            'created_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            ),
            'updated_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP',
                'on_update' => 'CURRENT_TIMESTAMP'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->create_table('users');

        // Categories table
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE,
                'auto_increment' => TRUE
            ),
            'name' => array(
                'type' => 'VARCHAR',
                'constraint' => '100',
                'unique' => TRUE
            ),
            'slug' => array(
                'type' => 'VARCHAR',
                'constraint' => '100',
                'unique' => TRUE
            ),
            'description' => array(
                'type' => 'TEXT',
                'null' => TRUE
            ),
            'parent_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE,
                'null' => TRUE
            ),
            'is_active' => array(
                'type' => 'BOOLEAN',
                'default' => TRUE
            ),
            'created_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->add_key('parent_id');
        $this->dbforge->create_table('categories');

        // Videos table
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE,
                'auto_increment' => TRUE
            ),
            'user_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'title' => array(
                'type' => 'VARCHAR',
                'constraint' => '255'
            ),
            'description' => array(
                'type' => 'TEXT',
                'null' => TRUE
            ),
            'filename' => array(
                'type' => 'VARCHAR',
                'constraint' => '255'
            ),
            'thumbnail' => array(
                'type' => 'VARCHAR',
                'constraint' => '255',
                'null' => TRUE
            ),
            'duration' => array(
                'type' => 'INT',
                'constraint' => 11,
                'null' => TRUE
            ),
            'views' => array(
                'type' => 'INT',
                'constraint' => 11,
                'default' => 0
            ),
            'likes' => array(
                'type' => 'INT',
                'constraint' => 11,
                'default' => 0
            ),
            'dislikes' => array(
                'type' => 'INT',
                'constraint' => 11,
                'default' => 0
            ),
            'status' => array(
                'type' => 'ENUM',
                'constraint' => array('draft', 'published', 'processing', 'rejected'),
                'default' => 'draft'
            ),
            'visibility' => array(
                'type' => 'ENUM',
                'constraint' => array('public', 'unlisted', 'private'),
                'default' => 'public'
            ),
            'is_processing' => array(
                'type' => 'BOOLEAN',
                'default' => FALSE
            ),
            'scheduled_at' => array(
                'type' => 'DATETIME',
                'null' => TRUE
            ),
            'created_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            ),
            'updated_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP',
                'on_update' => 'CURRENT_TIMESTAMP'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->add_key('user_id');
        $this->dbforge->create_table('videos');

        // Tags table
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE,
                'auto_increment' => TRUE
            ),
            'name' => array(
                'type' => 'VARCHAR',
                'constraint' => '50',
                'unique' => TRUE
            ),
            'slug' => array(
                'type' => 'VARCHAR',
                'constraint' => '50',
                'unique' => TRUE
            ),
            'created_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->create_table('tags');

        // Video tags pivot table
        $this->dbforge->add_field(array(
            'video_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'tag_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            )
        ));
        $this->dbforge->add_key(['video_id', 'tag_id']);
        $this->dbforge->create_table('video_tags');

        // Video categories pivot table
        $this->dbforge->add_field(array(
            'video_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'category_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            )
        ));
        $this->dbforge->add_key(['video_id', 'category_id']);
        $this->dbforge->create_table('video_categories');

        // Comments table
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE,
                'auto_increment' => TRUE
            ),
            'video_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'user_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'parent_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE,
                'null' => TRUE
            ),
            'content' => array(
                'type' => 'TEXT'
            ),
            'status' => array(
                'type' => 'ENUM',
                'constraint' => array('pending', 'approved', 'rejected'),
                'default' => 'pending'
            ),
            'created_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            ),
            'updated_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP',
                'on_update' => 'CURRENT_TIMESTAMP'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->add_key('video_id');
        $this->dbforge->add_key('user_id');
        $this->dbforge->add_key('parent_id');
        $this->dbforge->create_table('comments');

        // Playlists table
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE,
                'auto_increment' => TRUE
            ),
            'user_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'name' => array(
                'type' => 'VARCHAR',
                'constraint' => '255'
            ),
            'description' => array(
                'type' => 'TEXT',
                'null' => TRUE
            ),
            'visibility' => array(
                'type' => 'ENUM',
                'constraint' => array('public', 'unlisted', 'private'),
                'default' => 'public'
            ),
            'created_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            ),
            'updated_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP',
                'on_update' => 'CURRENT_TIMESTAMP'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->add_key('user_id');
        $this->dbforge->create_table('playlists');

        // Playlist videos table
        $this->dbforge->add_field(array(
            'playlist_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'video_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'position' => array(
                'type' => 'INT',
                'constraint' => 11,
                'default' => 0
            ),
            'added_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            )
        ));
        $this->dbforge->add_key(['playlist_id', 'video_id']);
        $this->dbforge->create_table('playlist_videos');

        // Subscriptions table
        $this->dbforge->add_field(array(
            'subscriber_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'creator_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'created_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            )
        ));
        $this->dbforge->add_key(['subscriber_id', 'creator_id']);
        $this->dbforge->create_table('subscriptions');

        // Video reports table
        $this->dbforge->add_field(array(
            'id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE,
                'auto_increment' => TRUE
            ),
            'video_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'reporter_id' => array(
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => TRUE
            ),
            'reason' => array(
                'type' => 'TEXT'
            ),
            'status' => array(
                'type' => 'ENUM',
                'constraint' => array('pending', 'reviewed', 'resolved'),
                'default' => 'pending'
            ),
            'created_at' => array(
                'type' => 'DATETIME',
                'default' => 'CURRENT_TIMESTAMP'
            )
        ));
        $this->dbforge->add_key('id', TRUE);
        $this->dbforge->add_key('video_id');
        $this->dbforge->add_key('reporter_id');
        $this->dbforge->create_table('video_reports');

        // Add foreign keys
        $this->db->query('ALTER TABLE `videos` ADD CONSTRAINT `fk_videos_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `categories` ADD CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL');
        $this->db->query('ALTER TABLE `video_categories` ADD CONSTRAINT `fk_video_categories_video` FOREIGN KEY (`video_id`) REFERENCES `videos`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `video_categories` ADD CONSTRAINT `fk_video_categories_category` FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `video_tags` ADD CONSTRAINT `fk_video_tags_video` FOREIGN KEY (`video_id`) REFERENCES `videos`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `video_tags` ADD CONSTRAINT `fk_video_tags_tag` FOREIGN KEY (`tag_id`) REFERENCES `tags`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `comments` ADD CONSTRAINT `fk_comments_video` FOREIGN KEY (`video_id`) REFERENCES `videos`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `comments` ADD CONSTRAINT `fk_comments_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `comments` ADD CONSTRAINT `fk_comments_parent` FOREIGN KEY (`parent_id`) REFERENCES `comments`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `playlists` ADD CONSTRAINT `fk_playlists_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `playlist_videos` ADD CONSTRAINT `fk_playlist_videos_playlist` FOREIGN KEY (`playlist_id`) REFERENCES `playlists`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `playlist_videos` ADD CONSTRAINT `fk_playlist_videos_video` FOREIGN KEY (`video_id`) REFERENCES `videos`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `subscriptions` ADD CONSTRAINT `fk_subscriptions_subscriber` FOREIGN KEY (`subscriber_id`) REFERENCES `users`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `subscriptions` ADD CONSTRAINT `fk_subscriptions_creator` FOREIGN KEY (`creator_id`) REFERENCES `users`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `video_reports` ADD CONSTRAINT `fk_video_reports_video` FOREIGN KEY (`video_id`) REFERENCES `videos`(`id`) ON DELETE CASCADE');
        $this->db->query('ALTER TABLE `video_reports` ADD CONSTRAINT `fk_video_reports_reporter` FOREIGN KEY (`reporter_id`) REFERENCES `users`(`id`) ON DELETE CASCADE');
    }

    public function down() {
        $this->dbforge->drop_table('video_reports');
        $this->dbforge->drop_table('subscriptions');
        $this->dbforge->drop_table('playlist_videos');
        $this->dbforge->drop_table('playlists');
        $this->dbforge->drop_table('comments');
        $this->dbforge->drop_table('video_categories');
        $this->dbforge->drop_table('video_tags');
        $this->dbforge->drop_table('tags');
        $this->dbforge->drop_table('videos');
        $this->dbforge->drop_table('categories');
        $this->dbforge->drop_table('users');
    }
}
