-- GestorPro - Migration for MariaDB 10.6+
-- Run this in phpMyAdmin SQL tab on cPanel

-- Create database (if not exists)
CREATE DATABASE IF NOT EXISTS gestorpro_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE gestorpro_db;

-- =====================================================
-- 1. ENUMS (as lookup tables for MariaDB compatibility)
-- =====================================================

CREATE TABLE TenantPlan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO TenantPlan (name) VALUES
('FREE'),
('PRO'),
('ENTERPRISE');

CREATE TABLE UserRole (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO UserRole (name) VALUES
('ADMIN'),
('MANAGER'),
('AGENT'),
('OWNER'),
('VIEWER');

CREATE TABLE Currency (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO Currency (name) VALUES
('PEN'),
('USD');

CREATE TABLE PropertyStatus (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO PropertyStatus (name) VALUES
('AVAILABLE'),
('RESERVED'),
('SOLD'),
('RENTED'),
('INACTIVE');

CREATE TABLE PropertyType (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO PropertyType (name) VALUES
('HOUSE'),
('APARTMENT'),
('LAND'),
('OFFICE'),
('LOCAL'),
('WAREHOUSE'),
('BUILDING');

CREATE TABLE DocumentType (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO DocumentType (name) VALUES
('TITLE_DEED'),
('TAX_RECEIPT'),
('HABITABILITY'),
('ENERGY_CERTIFICATE'),
('OWNERSHIP_CERTIFICATE'),
('CONTRACT'),
('OTHER');

CREATE TABLE DocumentStatus (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO DocumentStatus (name) VALUES
('PENDING'),
('UPLOADED'),
('VERIFIED'),
('REJECTED');

CREATE TABLE TaskPriority (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(10) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO TaskPriority (name) VALUES
('LOW'),
('MEDIUM'),
('HIGH'),
('URGENT');

CREATE TABLE ReportType (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO ReportType (name) VALUES
('SALES'),
('INVENTORY'),
('CLIENTS'),
('TASKS'),
('CUSTOM');

-- =====================================================
-- 2. MAIN TABLES
-- =====================================================

CREATE TABLE Tenant (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    plan ENUM('FREE', 'PRO', 'ENTERPRISE') DEFAULT 'FREE',
    isActive BOOLEAN DEFAULT TRUE,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE User (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    avatarUrl VARCHAR(255),
    role ENUM('ADMIN', 'MANAGER', 'AGENT', 'OWNER', 'VIEWER') DEFAULT 'AGENT',
    isActive BOOLEAN DEFAULT TRUE,
    tenantId INT NOT NULL,
    refreshToken VARCHAR(255),
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenantId) REFERENCES Tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Property (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(15, 2) NOT NULL,
    currency ENUM('PEN', 'USD') DEFAULT 'PEN',
    status ENUM('AVAILABLE', 'RESERVED', 'SOLD', 'RENTED', 'INACTIVE') DEFAULT 'AVAILABLE',
    type ENUM('HOUSE', 'APARTMENT', 'LAND', 'OFFICE', 'LOCAL', 'WAREHOUSE', 'BUILDING') NOT NULL,
    address VARCHAR(255),
    district VARCHAR(255),
    city VARCHAR(255),
    province VARCHAR(255),
    area DECIMAL(10, 2),
    bedrooms INT,
    bathrooms INT,
    parkingSpaces INT,
    yearBuilt INT,
    services JSON,
    amenities JSON,
    isPublished BOOLEAN DEFAULT FALSE,
    ownerId INT,
    tenantId INT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenantId) REFERENCES Tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE PropertyImage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(255) NOT NULL,
    filename VARCHAR(255) NOT NULL,
    isMain BOOLEAN DEFAULT FALSE,
    `order` INT DEFAULT 0,
    propertyId INT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (propertyId) REFERENCES Property(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE PropertyPlan (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    url VARCHAR(255) NOT NULL,
    filename VARCHAR(255) NOT NULL,
    propertyId INT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (propertyId) REFERENCES Property(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE PropertyDocument (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type ENUM('TITLE_DEED', 'TAX_RECEIPT', 'HABITABILITY', 'ENERGY_CERTIFICATE', 'OWNERSHIP_CERTIFICATE', 'CONTRACT', 'OTHER') NOT NULL,
    url VARCHAR(255) NOT NULL,
    filename VARCHAR(255) NOT NULL,
    isRequired BOOLEAN DEFAULT FALSE,
    status ENUM('PENDING', 'UPLOADED', 'VERIFIED', 'REJECTED') DEFAULT 'PENDING',
    propertyId INT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (propertyId) REFERENCES Property(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Client (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tipoIdentidad VARCHAR(20) NOT NULL,
    nroIdentidad VARCHAR(50) NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    direccion VARCHAR(255),
    telefono VARCHAR(50),
    email VARCHAR(255),
    notas TEXT,
    tenantId INT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenantId) REFERENCES Tenant(id) ON DELETE CASCADE,
    UNIQUE KEY unique_client_identidad (nroIdentidad, tenantId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Task (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    dueDate DATETIME,
    priority ENUM('LOW', 'MEDIUM', 'HIGH', 'URGENT') DEFAULT 'MEDIUM',
    isCompleted BOOLEAN DEFAULT FALSE,
    completedAt DATETIME,
    userId INT NOT NULL,
    propertyId INT,
    tenantId INT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES User(id) ON DELETE CASCADE,
    FOREIGN KEY (propertyId) REFERENCES Property(id) ON DELETE SET NULL,
    FOREIGN KEY (tenantId) REFERENCES Tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Report (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    type ENUM('SALES', 'INVENTORY', 'CLIENTS', 'TASKS', 'CUSTOM') NOT NULL,
    filters JSON,
    data JSON,
    tenantId INT NOT NULL,
    createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenantId) REFERENCES Tenant(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- 3. SEED DATA (Tenant and Admin User)
-- =====================================================

INSERT INTO Tenant (name, slug, plan) VALUES
('Default Tenant', 'default', 'PRO');

INSERT INTO User (email, password, name, role, tenantId) VALUES
('admin@gestorpro.com', '$2a$10$EkD.EfvNEf5P8vJQV1QJYuwtIBV2FFaL2t9kX.APox.2t/.L.HzLC', 'Administrador', 'ADMIN', 1);

-- =====================================================
-- 4. INDEXES (for performance)
-- =====================================================

CREATE INDEX idx_property_tenant ON Property(tenantId);
CREATE INDEX idx_property_status ON Property(status);
CREATE INDEX idx_property_type ON Property(type);
CREATE INDEX idx_property_city ON Property(city);
CREATE INDEX idx_client_tenant ON Client(tenantId);
CREATE INDEX idx_task_user ON Task(userId);
CREATE INDEX idx_task_completed ON Task(isCompleted);
CREATE INDEX idx_report_tenant ON Report(tenantId);