-- Kartu Studio SQL for MySQL/MariaDB
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS document_verifications, awards, student_cards, template_elements, templates, students, role_permissions, permissions, users, tenants;
SET FOREIGN_KEY_CHECKS=1;

CREATE TABLE tenants (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,slug VARCHAR(255) NOT NULL UNIQUE,logo_path VARCHAR(255) NULL,is_active TINYINT(1) NOT NULL DEFAULT 1,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL) ENGINE=InnoDB;
CREATE TABLE users (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id BIGINT UNSIGNED NULL,name VARCHAR(255) NOT NULL,email VARCHAR(255) NOT NULL UNIQUE,password VARCHAR(255) NOT NULL,role VARCHAR(50) NOT NULL DEFAULT 'admin',remember_token VARCHAR(100) NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT fk_users_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE students (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id BIGINT UNSIGNED NOT NULL,name VARCHAR(255) NOT NULL,nis VARCHAR(100) NULL,nisn VARCHAR(100) NULL,class_name VARCHAR(100) NULL,major VARCHAR(150) NULL,birth_date DATE NULL,birth_place VARCHAR(150) NULL,address TEXT NULL,photo_path VARCHAR(255) NULL,is_active TINYINT(1) DEFAULT 1,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX idx_students_tenant_name(tenant_id,name),CONSTRAINT fk_students_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE templates (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id BIGINT UNSIGNED NULL,created_by BIGINT UNSIGNED NULL,name VARCHAR(255) NOT NULL,type VARCHAR(50) NOT NULL,category VARCHAR(100) NULL,page_width DECIMAL(10,2) NOT NULL,page_height DECIMAL(10,2) NOT NULL,orientation VARCHAR(30) DEFAULT 'landscape',background_path VARCHAR(255) NULL,background_color VARCHAR(50) NULL,thumbnail_path VARCHAR(255) NULL,settings JSON NULL,tags JSON NULL,is_active TINYINT(1) DEFAULT 1,is_default TINYINT(1) DEFAULT 0,is_system TINYINT(1) DEFAULT 0,is_public TINYINT(1) DEFAULT 0,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT fk_templates_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE SET NULL,CONSTRAINT fk_templates_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL) ENGINE=InnoDB;
CREATE TABLE template_elements (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,template_id BIGINT UNSIGNED NOT NULL,type VARCHAR(50) NOT NULL,name VARCHAR(255) NULL,field VARCHAR(255) NULL,x DECIMAL(10,2) DEFAULT 0,y DECIMAL(10,2) DEFAULT 0,width DECIMAL(10,2) DEFAULT 100,height DECIMAL(10,2) DEFAULT 50,rotation DECIMAL(8,2) DEFAULT 0,z_index INT DEFAULT 0,is_locked TINYINT(1) DEFAULT 0,is_visible TINYINT(1) DEFAULT 1,properties JSON NULL,styles JSON NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT fk_elements_template FOREIGN KEY(template_id) REFERENCES templates(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE student_cards (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id BIGINT UNSIGNED NOT NULL,student_id BIGINT UNSIGNED NOT NULL,template_id BIGINT UNSIGNED NOT NULL,card_number VARCHAR(255) NOT NULL UNIQUE,status VARCHAR(30) DEFAULT 'draft',pdf_path VARCHAR(255) NULL,verification_token VARCHAR(255) NOT NULL UNIQUE,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT fk_cards_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,CONSTRAINT fk_cards_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE,CONSTRAINT fk_cards_template FOREIGN KEY(template_id) REFERENCES templates(id)) ENGINE=InnoDB;
CREATE TABLE awards (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id BIGINT UNSIGNED NOT NULL,student_id BIGINT UNSIGNED NOT NULL,template_id BIGINT UNSIGNED NOT NULL,award_number VARCHAR(255) NOT NULL UNIQUE,title VARCHAR(255) NOT NULL,achievement TEXT NULL,event_name VARCHAR(255) NULL,event_date DATE NULL,issued_date DATE NULL,status VARCHAR(30) DEFAULT 'draft',pdf_path VARCHAR(255) NULL,verification_token VARCHAR(255) NOT NULL UNIQUE,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,CONSTRAINT fk_awards_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,CONSTRAINT fk_awards_student FOREIGN KEY(student_id) REFERENCES students(id) ON DELETE CASCADE,CONSTRAINT fk_awards_template FOREIGN KEY(template_id) REFERENCES templates(id)) ENGINE=InnoDB;
CREATE TABLE permissions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL UNIQUE,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL) ENGINE=InnoDB;
CREATE TABLE role_permissions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,role VARCHAR(50) NOT NULL,permission_id BIGINT UNSIGNED NOT NULL,UNIQUE KEY uq_role_permission(role,permission_id),CONSTRAINT fk_role_permission FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE document_verifications (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,tenant_id BIGINT UNSIGNED NOT NULL,document_type VARCHAR(50) NOT NULL,document_id BIGINT UNSIGNED NOT NULL,token VARCHAR(255) NOT NULL UNIQUE,verified_at TIMESTAMP NULL,created_at TIMESTAMP NULL,updated_at TIMESTAMP NULL,INDEX idx_verification_doc(document_type,document_id),CONSTRAINT fk_verifications_tenant FOREIGN KEY(tenant_id) REFERENCES tenants(id) ON DELETE CASCADE) ENGINE=InnoDB;

INSERT INTO tenants(name,slug,is_active,created_at,updated_at) VALUES('Demo School','demo-school',1,NOW(),NOW());
INSERT INTO users(tenant_id,name,email,password,role,created_at,updated_at) VALUES(1,'Administrator','admin@example.com','$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llCw1m2q4WwJ9xV5l7wQ2','admin',NOW(),NOW());
INSERT INTO permissions(name,created_at,updated_at) VALUES
('student.manage',NOW(),NOW()),('student-card.create',NOW(),NOW()),('student-card.publish',NOW(),NOW()),('award.create',NOW(),NOW()),('award.publish',NOW(),NOW()),('template.manage',NOW(),NOW());
INSERT INTO role_permissions(role,permission_id) SELECT 'admin',id FROM permissions;
INSERT INTO templates(tenant_id,created_by,name,type,category,page_width,page_height,orientation,background_color,tags,is_active,is_default,is_system,is_public,created_at,updated_at) VALUES
(1,1,'Modern Student Card','card','modern',242.65,153,'landscape','#ffffff','["modern","school","student-card"]',1,1,1,1,NOW(),NOW()),
(1,1,'Minimal Student Card','card','minimal',242.65,153,'landscape','#ffffff','["minimal","school","student-card"]',1,0,1,1,NOW(),NOW()),
(1,1,'Elegant Gold Certificate','certificate','elegant',841.89,595.28,'landscape','#fffdf7','["certificate","elegant"]',1,1,1,1,NOW(),NOW()),
(1,1,'Modern Blue Certificate','certificate','modern',841.89,595.28,'landscape','#ffffff','["certificate","modern"]',1,0,1,1,NOW(),NOW()),
(1,1,'Achievement Award','award','achievement',841.89,595.28,'landscape','#ffffff','["award","achievement"]',1,1,1,1,NOW(),NOW()),
(1,1,'Formal Award','award','formal',841.89,595.28,'landscape','#ffffff','["award","formal"]',1,0,1,1,NOW(),NOW());