# Defining Tables

How to map C# classes and enums to PostgreSQL tables using [Table], [FlagTable], [DatabaseEnum], and related attributes in Socigy.OpenSource.DB v0.2.0.

## Overview

Every entity that gets its own SQL table must carry a `[Table]` attribute. The source generator reads this attribute to produce all DDL, query builders, insert builders, and migration helpers for that entity. Without it, the class is invisible to the framework.

## The `[Table]` Attribute

`[Table("sql_name")]` maps a C# class (or enum) to a PostgreSQL table. The string argument becomes the literal table name used in every generated SQL statement.

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }
    public string? Email { get; set; }
}
```

### Classes Must Be `partial`

The source generator augments your class with generated methods. If the class is not declared `partial`, the build will fail because the generator cannot emit the second part of the class.

```csharp
// CORRECT
[Table("users")]
public partial class User { ... }

// WRONG — build error: cannot add members to a non-partial type
[Table("users")]
public class User { ... }
```

### Enum as Reference Table

Applying `[Table]` to a plain (non-flags) enum tells the migration CLI to create a reference table with three columns: `id` (the underlying integer value), `value` (the enum member name as a string), and `description` (populated from `[Description("...")]` attributes on the enum members, or `NULL` if absent). The CLI seeds the rows automatically.

```csharp
[Table("user_visibility")]
public enum UserVisibility : short
{
    [Description("Visible to everyone")]
    Public,
    CirclesOnly,
    CustomCircles
}
```

The generated DDL produces a table equivalent to:

```sql
CREATE TABLE "user_visibility" (
    "id"          SMALLINT NOT NULL,
    "value"       TEXT     NOT NULL,
    "description" TEXT,
    PRIMARY KEY ("id")
);

INSERT INTO "user_visibility" ("id", "value", "description") VALUES
    (0, 'Public',        'Visible to everyone'),
    (1, 'CirclesOnly',   NULL),
    (2, 'CustomCircles', NULL);
```

A class that stores a `UserVisibility` column will hold the `id` value (a `SMALLINT`) and can join to the reference table when needed.

## The `[FlagTable]` Attribute

`[FlagTable("junction_table_name")]` marks an explicit junction class for a flags-enum relationship. This is the alternative to the auto-generated junction table that `[FlaggedEnum]` creates when no explicit class is provided.

Use `[FlagTable]` when you need extra columns on the junction table (for example, an `AssignedAt` timestamp):

```csharp
[Flags]
[Table("user_role")]
public enum UserRole : short
{
    User      = 1,
    Admin     = 2,
    Developer = 4,
    Reviewer  = 8
}

[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }

    // Points to the explicit junction class
    [FlaggedEnumTable(typeof(UserParentRole))]
    public UserRole ParentRole { get; set; }
}

// Explicit junction — carries an extra column
[FlagTable("users_parent_role")]
public partial class UserParentRole
{
    [PrimaryKey, ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid UserId { get; set; }

    [PrimaryKey, ForeignKey(typeof(UserRole), OnDelete = DbValues.ForeignKey.Cascade)]
    public short UserRoleId { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime AssignedAt { get; set; }
}
```

`[FlagTable]` classes must also be `partial`.

## The `[DatabaseEnum]` Attribute

`[DatabaseEnum]` on an enum instructs the migration CLI to create a PostgreSQL `ENUM` type instead of a reference table. A column of that type stores the enum member name directly as a PostgreSQL enumeration value.

```csharp
[DatabaseEnum]
public enum OrderStatus
{
    Pending,
    Processing,
    Shipped,
    Delivered,
    Cancelled
}

[Table("orders")]
public partial class Order
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public OrderStatus Status { get; set; }
}
```

The migration CLI emits:

```sql
CREATE TYPE "order_status" AS ENUM (
    'Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled'
);

CREATE TABLE "orders" (
    "id"     UUID        NOT NULL DEFAULT gen_random_uuid(),
    "status" order_status NOT NULL,
    PRIMARY KEY ("id")
);
```

> **NOTE** `[DatabaseEnum]` and `[Table]` on an enum are mutually exclusive. Use `[DatabaseEnum]` when you want a PostgreSQL native ENUM type; use `[Table]` when you want a reference table seeded with integer–name pairs.

## The `[Renamed]` Attribute on a Class

`[Renamed("old_table_name")]` placed on a class tells the migration CLI that the table was previously named `old_table_name`. The CLI will emit an `ALTER TABLE ... RENAME TO ...` statement instead of a `DROP` + `CREATE`, preserving all existing rows.

```csharp
// Previously the table was called "app_users"
[Renamed("app_users")]
[Table("users")]
public partial class User
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    public string Username { get; set; }
}
```

Generated migration SQL:

```sql
ALTER TABLE "app_users" RENAME TO "users";
```

> **WARNING** Remove `[Renamed]` after the migration has been applied and committed. Leaving it in place will cause the migration CLI to emit the `RENAME` statement again on the next run.

## Complete Example

The following class demonstrates several table-level attributes together:

```csharp
using Socigy.OpenSource.DB.Attributes;

[Table("blog_posts")]
[Check("LENGTH(slug) > 0")]
[Unique(nameof(AuthorId), nameof(Slug), Name = "uq_author_slug")]
public partial class BlogPost
{
    [PrimaryKey, Default(DbDefaults.Guid.Random)]
    public Guid Id { get; set; }

    [ForeignKey(typeof(User), OnDelete = DbValues.ForeignKey.Cascade)]
    public Guid AuthorId { get; set; }

    [StringLength(200)]
    public string Title { get; set; }

    [StringLength(200)]
    public string Slug { get; set; }

    [Default(DbDefaults.Time.Now)]
    public DateTime PublishedAt { get; set; }

    public bool IsPublished { get; set; }
}
```