-- =============================================================
-- Migration 002 — Mídia e documentos dos imóveis
-- =============================================================

USE `geral_db`;

CREATE TABLE IF NOT EXISTS `alugueis_fotos` (
    `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `imovel_id`      INT UNSIGNED  NOT NULL,
    `nome_original`  VARCHAR(255)  NOT NULL,
    `path`           VARCHAR(500)  NOT NULL COMMENT 'relativo a public/',
    `tamanho`        INT UNSIGNED  NOT NULL DEFAULT 0 COMMENT 'bytes',
    `ordem`          TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `created_by`     INT UNSIGNED      NULL COMMENT 'portal_auth.users.id',
    `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_af_imovel` (`imovel_id`),
    CONSTRAINT `fk_af_imovel` FOREIGN KEY (`imovel_id`)
        REFERENCES `alugueis_imoveis`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `alugueis_documentos` (
    `id`             INT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `imovel_id`      INT UNSIGNED  NOT NULL,
    `nome_original`  VARCHAR(255)  NOT NULL,
    `path`           VARCHAR(500)  NOT NULL,
    `tipo`           VARCHAR(80)       NULL COMMENT 'contrato, laudo, comprovante, outro',
    `tamanho`        INT UNSIGNED  NOT NULL DEFAULT 0,
    `created_by`     INT UNSIGNED      NULL,
    `created_at`     DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ad_imovel` (`imovel_id`),
    CONSTRAINT `fk_ad_imovel` FOREIGN KEY (`imovel_id`)
        REFERENCES `alugueis_imoveis`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
