-- Migration 0004: create_shared_polymorphic
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 3 — SHARED / POLYMORPHIC: ACTIVITY TIMELINE, NOTES, FILES, TAGS
-- =====================================================================

-- Powers every "Activity Timeline" seen on Lead Details, Customer
-- Details, and (filtered) the global Activity Logs page.
CREATE TABLE IF NOT EXISTS activities (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type  VARCHAR(40)     NOT NULL COMMENT 'lead, customer, company, appointment, invoice...',
    entity_id    BIGINT UNSIGNED NOT NULL,
    type         VARCHAR(60)     NOT NULL COMMENT 'call_logged, email_sent, stage_changed, note_added...',
    description  VARCHAR(500)    NOT NULL,
    metadata     JSON            NULL COMMENT 'Structured payload, e.g. {"from":"New","to":"Quoted"}',
    caused_by    BIGINT UNSIGNED NULL COMMENT 'NULL = system/automation generated',
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_activities_entity (entity_type, entity_id, created_at),
    KEY idx_activities_caused_by (caused_by),
    CONSTRAINT fk_activities_caused_by FOREIGN KEY (caused_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS notes (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type  VARCHAR(40)     NOT NULL,
    entity_id    BIGINT UNSIGNED NOT NULL,
    body         TEXT            NOT NULL,
    created_by   BIGINT UNSIGNED NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at   DATETIME        NULL,
    KEY idx_notes_entity (entity_type, entity_id),
    CONSTRAINT fk_notes_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backs the "Files" tab on Lead Details and any future attachment UI
-- (email attachments, invoice PDFs, imported spreadsheets).
CREATE TABLE IF NOT EXISTS attachments (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type   VARCHAR(40)     NOT NULL,
    entity_id     BIGINT UNSIGNED NOT NULL,
    disk_path     VARCHAR(500)    NOT NULL COMMENT 'Path under /uploads, never web-executable',
    original_name VARCHAR(255)    NOT NULL,
    mime_type     VARCHAR(120)    NOT NULL,
    size_bytes    BIGINT UNSIGNED NOT NULL,
    checksum_sha256 CHAR(64)      NULL,
    uploaded_by   BIGINT UNSIGNED NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at    DATETIME        NULL,
    KEY idx_attachments_entity (entity_type, entity_id),
    CONSTRAINT fk_attachments_uploaded_by FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tags (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(80)  NOT NULL,
    slug       VARCHAR(80)  NOT NULL,
    color_hex  CHAR(7)      NULL,
    UNIQUE KEY uk_tags_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS taggables (
    tag_id       BIGINT UNSIGNED NOT NULL,
    entity_type  VARCHAR(40)     NOT NULL,
    entity_id    BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (tag_id, entity_type, entity_id),
    KEY idx_taggables_entity (entity_type, entity_id),
    CONSTRAINT fk_taggables_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;
